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:
,DB_Name(database_id) as database_name
-- 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)
The sys.databases table has information about the database in the current SQL Server instance.