SQL Server: Find Database File Path

Suppose you want to find out the path where the SQL Server database files(mdf and ldf) are located, using a query. You can use system procedure sp_helpdb and system view sysfiles to obtain this information.

The following methods shows the path of the database files for master database

Method 1 : Use sp_helpdb

EXEC sp_helpdb master

db_file
Method 2 : Use sys.sysfiles view

select filename from master.sys.sysfiles

sql-db-filepath
at

2 comments:

  1. VishalSeptember 22, 2011 at 4:33 AM

    For SQL Server 2005+ you can also use sys.database_files:

    SELECT name, physical_name
    FROM sys.database_files

    ReplyDelete
  2. Suprotim AgarwalSeptember 22, 2011 at 5:03 AM

    I am not 100% sure but I think sys.sysfiles was either removed or planned to be removed from SQL 2005+ onwards. So yes sys.database_files is a good option.

    For read-only db's in particular, I would recommend trying the master.sys.master_files DMV.

    ReplyDelete