How to find mdf / ldf locations in T-SQL?

Question

How can I find mdf / ldf locations in SQL2008 using T-SQL? Life is too short and error prone to do so on the Properties screen.

Answer 1

SELECT   DB_NAME(database_id) AS DatabaseName
        ,Name AS Logical_Name
        ,Physical_Name
        ,(size*8)/1024 SizeMB
FROM   sys.master_files
WHERE  DB_NAME(database_id) = 'Your_database_Name'
GO

or

Use Database_Name
GO

SELECT  name
       ,type_desc
       ,physical_name
       ,(size*8)/1024 SizeMB
FROM   sys.database_files
GO

Answer 2

Try the following:

select filename FROM sysfiles