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:
1
SELECT
2 database_id,
3 DB_Name(database_id) as database_name,
4 [file_id],
5 type_desc,
6 [name],
7 physical_name,
8 state_desc,
9 [size],
10 max_size
11FROM
12 sys.master_files
13WHERE
14 -- you can use the DB_ID function to return the
15 -- database id of a database by name
16 (database_id = DB_ID(N'master'))
17 -- if you know the id you can filter by it directly
18 -- (database_id = 1)
19ORDER BY
20 type_desc DESC,
21 physical_name; 
The
sys.databases table has information about the database in the current
SQL Server instance.
1SELECT
2 database_id,
3 [name],
4 compatibility_level,
5 collation_name,
6 state_desc
7FROM
8 sys.databases; 
Labels: Code, SQL
AddrMon
October 2005
Subscribe!