Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

All database physical_name Path Expand / Collapse
Author
Message
Posted Wednesday, November 12, 2008 5:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 14, 2009 1:56 AM
Points: 438, 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.

---
Post #601251
Posted Wednesday, November 12, 2008 5:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
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

Post #601259
Posted Wednesday, November 12, 2008 5:31 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 7, 2011 1:41 AM
Points: 346, 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
Post #601263
Posted Wednesday, November 12, 2008 5:58 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 5,863, Visits: 12,941
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


---------------------------------------------------------------------

Post #601274
Posted Thursday, November 13, 2008 12:28 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 14, 2009 1:56 AM
Points: 438, Visits: 754
Thanks You Very Much GUYZZZ...

Great Work...!
Post #601879
Posted Thursday, November 13, 2008 12:35 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, June 18, 2013 7:41 AM
Points: 323, Visits: 464
Please use following command :

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


_____________________________________________________________________________________________________________
Paresh Prajapati
+919924626601
http://paresh-sqldba.blogspot.com/
LinkedIn | Tweet Me | FaceBook | Brijj
Post #601883
Posted Thursday, November 13, 2008 3:50 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 14, 2009 1:56 AM
Points: 438, 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.

---
Post #601955
Posted Thursday, November 13, 2008 3:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
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
Post #601959
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse