Query in MSSQL that returns .mdf and .ldf filename/location for specific database?

Question

Can I use a query in MSSQL to get the .mdf and .ldf filename/location for a specific database?

Answer 1

SELECT * FROM sys.database_files (SQL 2005+)

SELECT * FROM dbo.sysfiles (SQL 2000)

Answer 2

SELECT * FROM sys.master_files

...will give you a basic view of where your database lives. It might not cope too well with filegroups, etc.

Answer 3

You can use:

exec sp_helpfile

Will return a query containing information about the files of the current database.

This will work on any SQL server version.