T-SQL View all database files on a SQL Instance

Overview

Sometimes you want to be able to see where all your database files are by doing a query. You can use the sys.database_files table to tell you on an individual database level but if you want to see it for all databases then the following script can help.

T-SQL Script

CREATE TABLE ##dbFiles
    (
      DBName SYSNAME ,
      name VARCHAR(200) ,
      physical_name VARCHAR(2000) ,
      type_desc VARCHAR(200) ,
      state_desc VARCHAR(200)
    )
go

-- executes a command on every database on the instance. The ? denotes the database name each pass through the loop
EXEC sp_msforeachdb '
use [?]
insert into ##dbFiles
select ''?'' as [DBName],name , physical_name,type_desc, state_desc
from sys.database_files
'

SELECT  *
FROM    ##dbFiles

-- clean up
DROP TABLE ##dbFiles

Example of results returned by the script

You can see the physical_name column returned by the query indicates where the database file is stored on the server.