SQL: Where are the database files?
From within the SQL Server Management Studio you can right click on a database and view its properties to get a list of the physical files that make up a database. If you do not have access to SQL Management Studio and the database is online you can run the sp_helpfile stored procedure. The sp_helpfile stored procedure lists the files for the current (individual) database; the database must be online. What about retrieving file information for offline databases?
Another alternative for retrieving the list of database files for a single or collective set of databases is to query the sys.master_files table. You can query the sys.master_files database if the database is online, offline or in one of the other states. The following query retrieves database file information:
SELECT
database_id
,DB_Name(database_id) as database_name
,[file_id]
,type_desc
,[name]
,physical_name
,state_desc
,[size]
,max_size
FROM
sys.master_files
WHERE
-- you can use the DB_ID function to return the
-- database id of a database by name
(database_id = DB_ID(N'master'))
-- if you know the id you can filter by it directly
-- (database_id = 1)
ORDER BY
type_desc DESC
,physical_name;
The sys.databases table has information about the database in the current SQL Server instance.
SELECT
database_id
,[name]
,compatibility_level
,collation_name
,state_desc
FROM
sys.databases;
