SQL SERVER – Find Location of Data File Using T-SQL

While preparing for the training course of Microsoft SQL Server 2005/2008 Query Optimization and & Performance Tuning, I needed to find out where my database files are stored on my hard drive. It is when following script came in handy to find the location of the data file using T-SQL.

SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf',
LOWER(physical_name)) - 1) DataFileLocation
FROM master.sys.master_files
WHERE database_id = 1 AND FILE_ID = 1

Resultset:

DataFileLocation
--------------------------------------------------------------------------
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\
(1 row(s) affected)

Above script provided exact location of where my data files of master database is. In fact, you can use it to find any other path of database as needed. If you want, you can change the master. mdf to any other file and you can easily find the location of the other database as well.

Solarwinds

SQL SERVER - Find Location of Data File Using T-SQL datafilelocation

Here are few other blog posts related to this topic which you may find interesting:

I personally feel this is a very handy trick to find location of data file using T-SQL. If you have any other ticks available, do post in the comment and I will publish the blog post with due credit.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – FIX: Error: 1807 Could not obtain exclusive lock on database ‘model’. Retry the operation later.
Next Post
SQLAuthority News – TechDays Session at Infosys Mysore 2009 – Change Data Capture and PowerPivot

Related Posts

27 Comments. Leave new

  • thanks Again.

    Reply
  • This script works better
    list database name, owner,filename and location

    use master
    Select
    sys.databases.name as [database name],
    suser_sname(owner_sid) as [owner name],
    sys.master_files.name as [file name],
    sys.master_files.physical_name as [Location]
    from sys.databases
    right join sys.master_files on sys.databases.database_id = sys.master_files.database_id
    order by location,sys.database.name

    Reply
  • great information as always…thanks

    i have a strange problem…if someone can help…

    sys.master_files is showing i have about 15 files for temdb but sys.database_files showing only 4…and i can see physically its has actually only 4 files…so not sure how i can update/fix master db entries

    any idea?

    Reply
  • Hi,
    I have the same problem. Only 1 tempdb data file and 1 temdb log file on a disk and in tempdb properties but about 10 tempdb datafiles in sys.master_files (probably after restoring master database from instance to instance) Any idea how to clean up sys.master_files ?

    Reply

Leave a Reply

Menu