This post shares a SQL Script to retrieve filenames, paths and other information for all databases on a SQL Server instance. As well as this, I share SQL for parsing filenames from paths which may be useful to you for scripting.
Retrieving database file path information is often needed by DBAs. One of the main considerations when maintaining databases is their sizes, and making sure there’s enough disk space available when the databases grow. Knowing which drive our data and log files gives us more insight for space considerations, and it might also be needed info for things like ensuring these db locations are added as anti-virus whitelist file locations.
If you’re in an environment managing multiple MS SQL database servers, having standards set for the location of all SQL Server data and log files is key. It makes things easier for estimating and managing disk space provisions, and it makes db server migrations smoother when all database paths are the same.
Get Database File Names and Paths
The following SQL script provides information about database data and log files, including filenames, paths, sizes, and growth settings:
All my local SQL Server database files in this example are tiny. If this were in my day-to-day DBA environment, reading MB for database sizes would be too many numbers and would be better of as GB.
Parsing a File Name from Path in SQL
The SQL script below parses the filename from a path. Amend the directory variable and when executed you’ll get the filename not the path.
-- Parse filename from path
DECLARE @FullFilePath NVARCHAR(500) = 'C:\Data\MyDatabase.mdf';
SELECT RIGHT(@FullFilePath, CHARINDEX('\', REVERSE(@FullFilePath)) - 1) AS [File Name];
Stay tuned for more MSSQL DBA Tips and SQL Scripts to enhance your SQL Server administration skills!
Leave a Reply