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.

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.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

27 thoughts on “SQL SERVER – Find Location of Data File Using T-SQL

  1. Thanks for sharing this useful script, but I would like to ask one more thing that how could we know the name of the data file (.mdf file) of any database. let us know If there is any scrip for that.. Thanks.

  2. Hey Pinal,
    Hope you are doing great!
    is there any Chance to Get Access to Power point Presenatations or Any Videos.
    Please guide me
    Thanks

    Have a great day ahead.

  3. Hi pinal,
    really its very good script. I need to know more abt sp_help commands, can we have list of all those? It will be very useful ! Thanks

  4. Hi Pinal,

    I use for same purpose: – sp_helpdb ‘master’ OR SELECT physical_name FROM master.sys.master_files WHERE name like ‘master%’ AND FILE_ID = 1– for DATA file.

    Is it good?

    Code which you provide need database_id = 1, So firdt need to find out it using ‘sp_helpdb’
    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

    Thanks

    Vasant Jagtap

  5. hi pinal

    i wana import csv sheets data in sql server 2005 without using bulk cpoy.if u hv diff way plz do somethink

    regards
    vicky
    web deveolper

  6. Hi Pinal:
    How could I know how much free space my SQL database has right now? Also what is the maximum size of SQL 2005 database can be.
    I have a server with more than 3 TB HD space. Already 8 databases are there and more are going to be in. Mots of them are 100 to 150 GB. I’m afraid about thinking SQL 2005’s space capacity.

    Thanks in advance.

    Maksuda…

  7. Can any one tell that how should we query data in Dat Extenstion File in via Transact SQL??

    i just need to know Select Query like its not working

    Select * from ‘D:\Data\abc.dat’

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #010 « SQL Server Journey with SQL Authority

  9. 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

  10. 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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s