Enumerate All Database Files in Server

Did lots of searching, couldn't find the answer.

I am looking to enumerate all databases and their files on a given server. For example:

Database File Name File Path master master C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf master mastlog C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf msdb MSDBData C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf msdb MSDBLog C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf MyDB MyDB_Data C:\Databases\MyDB.mdf MyDB MyDB_Log C:\Databases\MyDB.ldf

I know you can use sys.database_files to pull this information database-by-database. But I am looking for a sql query that will generate this information at a server level.

TIA.

In SQL Server 2005, this is a snap.

select *

from sys.master_files

This doesn't suffer from any of the limitations of sys.database_files in that sys.database_files can be wrong if the database was restored and read only.

|||

Here you go.

Warning: sp_msforeachdb is undocumented procedure. Use it for single use. Don't stick on this SP.

Code Snippet

Create Table #Databases

(

[Database] nvarchar(1000),

[File Name] nvarchar(1000),

[File Path] nvarchar(1000)

);

Exec sp_msforeachdb 'Insert Into #Databases select ''?'', name,filename from [?]..sysfiles'

select * from #Databases

|||Thanks. Just what I was looking for.|||

Thanks for info on sp_msforeachdb. Very helpful.

Appreciate your time.