Problem
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?
Solution
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.
CREATE TABLE #DBFileInfo (
DBName sysname,
physical_name NVARCHAR(1000)
)
DECLARE cursDBs CURSOR FAST_FORWARD
FOR
SELECT name FROM master.sys.databases WHERE state = 0;
DECLARE @DBName sysname;
DECLARE @SQL NVARCHAR(MAX);
OPEN cursDBs;
FETCH NEXT FROM cursDBs INTO @DBName;
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @SQL = 'INSERT INTO #DBFileInfo (DBName, Physical_Name)
SELECT ''' + @DBName + ''', physical_name
FROM [' + @DBName + '].sys.database_files;';
EXEC (@SQL);
FETCH NEXT FROM cursDBs INTO @DBName;
END;
CLOSE cursDBs;
DEALLOCATE CursDBs;
SELECT DISTINCT LEFT(physical_name, 1) AS 'Drive'
FROM #DBFileInfo
ORDER BY Drive;
DROP TABLE #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:
Next Steps
- Go beyond drive letters and understand how mount points work in SQL Server 2008 and higher.
- Learn how to use a CLR solution to check free disk space from inside SQL Server.
- Find out how to autogenerate attach and detach scripts for your databases.
Brian Kelley is an author, columnist, Certified Information Systems Auditor (CISA), and former Microsoft Data Platform (SQL Server) MVP (2009-2016) focusing primarily on SQL Server and Windows security. Brian currently serves as a data architect as well as an independent infrastructure/security architect concentrating on Active Directory, SQL Server, and Windows Server. He has served in a myriad of other positions including senior database administrator, data warehouse architect, web developer, incident response team lead, and project manager. Brian has spoken at 24 Hours of PASS, IT/Dev Connections, SQLConnections, the Techno Security and Forensics Investigation Conference, the IT GRC Forum, SyntaxCon, and at various SQL Saturdays, Code Camps, and user groups.
- MSSQLTips Awards: Author of the Year Contender – 2015, 2017 | Champion (100+ tips) – 2014
