Determining Drive Letters in Use by SQL Server Databases

By:   |   Updated: 2013-09-16   |   Comments (6)   |   Related: More > Database Administration


I am auditing my SQL Server environment and I need to determine what drive letters are in use by each instance. I know I can query sys.database_files for each database. Is there a way to do this for all databases?

Yes, there is. Let's look at several ways to do this, concluding with the best method.

SQL Server Cursor Based Approach on sys.database_files to Determine Database File Location

There's that cliché that "if all you have is a hammer, everything looks like a nail". Querying sys.database_files for each database fits this cliché. We'll cover it because it reveals how ugly this solution is. In other words, don't use it.

  DBName sysname,
  physical_name NVARCHAR(1000)
SELECT name FROM master.sys.databases WHERE state = 0;
DECLARE @DBName sysname;
OPEN cursDBs;
  SET @SQL = 'INSERT INTO #DBFileInfo (DBName, Physical_Name)
                  SELECT ''' + @DBName + ''', physical_name
                  FROM [' + @DBName + '].sys.database_files;';
  EXEC (@SQL);
CLOSE cursDBs;
SELECT DISTINCT LEFT(physical_name, 1) AS 'Drive' 
FROM #DBFileInfo

Like I said, that's an ugly and onerous solution. Surely there's better. In fact, there is.

Using the sysaltfiles SQL Server System Table to Determine the Drive Location

In earlier versions of SQL Server there was a system table called sysaltfiles. You would see it referenced occasionally, like when you moved tempdb. It's still present in the newer versions of SQL Server, but it's deprecated. That means it could be cut at any time. Therefore, while it's a far better solution than the cursor, you shouldn't use it. If, for some reason, you're still supporting versions of SQL Server older than SQL Server 2005, it *is* the right solution for those versions:

-- sysaltfiles solution
SELECT DISTINCT LEFT([filename], 1)  AS 'Drive'
FROM sysaltfiles
ORDER BY [Drive];

Determine the SQL Server Drive Location with the sys.master_files Catalog View

If sysaltfiles is deprecated, what then should we use? As it turns out, there is a catalog view called sys.master_files which performs much the same function. We can query it, except the column name is physical_name, just the same as with sys.database_files. Again, it's a simple query:

-- sys.master_files solution
SELECT DISTINCT LEFT([physical_name], 1)  AS 'Drive'* 
FROM sys.master_files
ORDER BY [Drive];

And some sample results:

The New and Improved sys.master_files solution
Next Steps

Last Updated: 2013-09-16

get scripts

next tip button

About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

Thursday, December 19, 2013 - 11:56:18 AM - Uwe Ricken Back To Top

Why don't u use sys.dm_os_volume_stats?


USE tempdb;
FROM sys.sysfiles f CROSS APPLY sys.dm_os_volume_stats(db_id(), f.fileid) vs

Thursday, October 17, 2013 - 9:33:00 AM - veeresh Back To Top

*** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS

You can use below queries



distinct (left(physical_name,1))frommaster.sys.master_files



distinct (left(filename,1))frommaster.dbo.sysaltfiles

but both of them will not be able to list if they are using mount point

Tuesday, September 17, 2013 - 9:16:18 AM - K. Brian Kelley Back To Top

If you mean like a command prompt, no. Some client tools, like sqlcmd, provide such functionality. However, that functionality is to enter T-SQL commands, not commands like "dir" or "chdir."


Tuesday, September 17, 2013 - 4:52:49 AM - Marc Lemoine Back To Top

Is there a Prompt dialoge box in Sql server ?

Monday, September 16, 2013 - 2:11:15 PM - Vikrant S Patil Back To Top


xp_fixeddrives will list all drives irrespectibe of the fact whether sql serevr uses the drive  (or not).

Nevertheless, this is indeed a very useful extended procedures and is used very often.

The in thing right now ( and most efficient as well) though is to use powershell, it comes out of the box with so many cmdlets \ dlls  that expose most (if not all) of the functionality and details pertaining to the environment. 


Vikrant S Patil 

Monday, September 16, 2013 - 10:24:40 AM - Vinod Back To Top

The extended stored procedure master..xp_fixeddrives also useful.



get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

Learn more about SQL Server tools