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

Monday, 16 May 2016

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

    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

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


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


SQL, Programming
Share with: 

Useful SQL tips - How to list all your SQL databases with their file locations and sizes

Support this Site

Popular Articles

What is Kutamo?
Kilimanjaro 2023
Kilimanjaro 2015
Kilimanjaro 2013
Australian Postcodes
New Zealand Postcodes
Worldwide City Database

Recent Articles

Favourite Links

Kutamo Studios
Kilimanjar 2023