SQL Tips #4 - Listing all SQL databases with their file locations and sizes

This query is particularly handy for generating a report of your database files and how much space they are taking up on disk. You can use this to periodically compare against previous reports to generate a trend of growth you may be experiencing.

How it works

The query is very simple - it extracts the database metrics from the sys.database_files collection, and formats it in an easy-to-read style.

1. Create a temp table to store the results in, in this case named ##temp
2. Retrieve the raw metrics from sys.database_files, pass the database names to sp_msforeachdb and insert the results into the temp table - formatting each column appropriately (ie size and freespace into floating point values with two decimal places)
3. Return the temp table formated as we desire
4. Drop the temp table

The Query

CREATE TABLE ##temp
(
    DatabaseName sysname,
    Name sysname,
    physical_name nvarchar(500),
    size decimal (18,2),
    FreeSpace decimal (18,2)
)

EXEC sp_msforeachdb '
USE [?];
INSERT INTO ##temp (DatabaseName, Name, physical_name, Size, FreeSpace)
    SELECT DB_NAME() AS [DatabaseName], Name,  physical_name,
    CAST(CAST(Round(CAST(size AS decimal) * 8.0/1024.0,2) AS decimal(18,2)) AS nvarchar) Size,
    CAST(CAST(Round(CAST(size AS decimal) * 8.0/1024.0,2) AS decimal(18,2)) -
    CAST(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 AS decimal(18,2)) AS nvarchar) AS FreeSpace
    FROM sys.database_files
'
SELECT DatabaseName,Name,size,FreeSpace,physical_name FROM ##temp
DROP TABLE ##temp

The Output

The query will return results similar to those below. I've replaced my own table names with 'tablename' for privacy reasons.

DatabaseName    Name        Size        FreeSpace   physical_name
master	        master	    5.38	1.38	    ...DATA\master.mdf
master	        mastlog	    2.00	1.30	    ...DATA\mastlog.ldf
model	        modeldev    8.00	5.31	    ...DATA\model.mdf
model	        modellog    8.00	6.94	    ...DATA\modellog.ldf
msdb	        MSDBData    17.94	0.13	    ...DATA\MSDBData.mdf
msdb	        MSDBLog	    19.63	17.63	    ...DATA\MSDBLog.ldf

Disclaimer

These queries are provided as a guide, and are by no means perfect. I use these on Microsoft SQL Server 2012, 2014 and 2016.

Tags