SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


All database physical_name Path


All database physical_name Path

Author
Message
SqlUser-529296
SqlUser-529296
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1242 Visits: 754
Hi,

If i run this query i am getting physical_name and all the file details for particular database.

SELECT * FROM [sys].[database_files]

I want to know all the database physical path (.mdf and .ldf path) for a particular database server. Is it possible in sql server 2005.

Appreciate for your help.

---
steveb.
steveb.
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10648 Visits: 7195
try this code to get the file paths for each db on a server.


DROP TABLE #DBFiles
CREATE TABLE #DBFiles
(
[DBName] [nvarchar](100),
[Name] [nvarchar](100),
[Filename] [nvarchar](100)
)
ON [PRIMARY]

INSERT INTO
#DBFiles
EXEC sp_MSforeachdb 'Select ''?'' as DatabaseName, [?]..sysfiles.name,
[?]..sysfiles.filename From [?]..sysfiles'


SELECT
*
FROM
#DBFiles


krayknot
krayknot
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1902 Visits: 534
steveb (11/12/2008)
try this code to get the file paths for each db on a server.


DROP TABLE #DBFiles
CREATE TABLE #DBFiles
(
[DBName] [nvarchar](100),
[Name] [nvarchar](100),
[Filename] [nvarchar](100)
)
ON [PRIMARY]

INSERT INTO
#DBFiles
EXEC sp_MSforeachdb 'Select ''?'' as DatabaseName, [?]..sysfiles.name,
[?]..sysfiles.filename From [?]..sysfiles'


SELECT
*
FROM
#DBFiles




its really amazing steve.

very gud

kshitij kumar
kshitij@krayknot.com
www.krayknot.com
george sibbald
george sibbald
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24778 Visits: 13698
or simply:

SELECT * FROM master.sys.master_files

if you want that matched up to the database name

select c.name,a.name as logicalname,a.physical_name from
master.sys.master_files a
inner join master.sys.sysdatabases c on a.database_id=c.dbid
order by dbid

---------------------------------------------------------------------
SqlUser-529296
SqlUser-529296
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1242 Visits: 754
Thanks You Very Much GUYZZZ...

Great Work...! w00t
Paresh Prajapati
Paresh Prajapati
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1941 Visits: 464
Please use following command :

select db_name(dbid),filename from sys.sysaltfiles
order by 1

_____________________________________________________________________________________________________________
Paresh Prajapati
Arrow +919924626601
http://paresh-sqldba.blogspot.com/
LinkedIn | Tweet Me | FaceBook | Brijj
SqlUser-529296
SqlUser-529296
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1242 Visits: 754
steveb (11/12/2008)
try this code to get the file paths for each db on a server.


DROP TABLE #DBFiles
CREATE TABLE #DBFiles
(
[DBName] [nvarchar](100),
[Name] [nvarchar](100),
[Filename] [nvarchar](100)
)
ON [PRIMARY]

INSERT INTO
#DBFiles
EXEC sp_MSforeachdb 'Select ''?'' as DatabaseName, [?]..sysfiles.name,
[?]..sysfiles.filename From [?]..sysfiles'


SELECT
*
FROM
#DBFiles




Hi steveb,

I am not getting "sp_MSforeachdb" in Sql Server online help. Where shall i get the information for the same.

---
steveb.
steveb.
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10648 Visits: 7195
It's an undocumented stored procedure and does not appear in books on-line, am not sure why as i find it useful.

try this link for help
http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search