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.

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)

SQL Backup, SQL Data Storage, SQL Scripts, SQL Server
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 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.

    Reply
  • Hi Abdul,

    Use the sp_helpdb to get the name of database files.

    Regards,
    Pinal Dave

    Reply
  • 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.

    Reply
  • Thanks for the script.
    Ramdas

    Reply
  • 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

    Reply
    • In SQL Server help file, goto index and type sp_
      It will list out all procedures that start with sp_

      Reply
  • 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

    Reply
  • Some of us are stuck with SS2K. How would you modify this script for SS2K?

    Reply
  • Hello Tom,

    Use the sysfiles table as beow:

    SELECT [filename] FROM dbName.dbo.sysfiles

    Or you can use sp_helpdb dbName

    Regards,
    Pinal Dave

    Reply
  • hi pinal
    i wana to store excel csv file data in sql server 2005 .
    plz help me how this possible

    Reply
  • 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

    Reply
  • 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…

    Reply
  • Selvam Krishnan
    November 30, 2011 4:35 pm

    use master
    SELECT * FROM sys.database_files

    Reply
  • Hi, how can I find the address of a database? I the website like and name, do I need anything else?

    Reply
  • To selam – How does that give you the free space?

    Reply
  • it should be Selvam :-)

    Reply
  • Reply
  • that doesnt give you free space. sorry. i’ll butt out now
    please remove that article form the site

    Reply
  • Muzzammil Shah
    April 4, 2012 11:52 am

    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’

    Reply
  • Good script. I wonder how I can have the same result with a batch script

    Reply
  • Thanks so very much!

    Reply

Leave a Reply

Menu
Exit mobile version