Peter Larsson Blog

Patron Saint of Lost Yaks

Get all your databases and their sizes

by Peter Larsson in  , , ,

SELECT      @@SERVERNAME AS SqlServerInstance,
            db.name AS DatabaseName,
            SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE f.size / 128.0E END) AS DatabaseSize,
            SUM(CASE WHEN af.groupid = 0 THEN af.size / 128.0E ELSE 0 END) AS LogSize,
            SUM(af.size / 128.0E) AS TotalSize
FROM        master..sysdatabases AS db
INNER JOIN  master..sysaltfiles AS af ON af.[dbid] = db.[dbid]
WHERE       db.name NOT IN ('distribution', 'Resource', 'master', 'tempdb', 'model', 'msdb') -- System databases
            AND db.name NOT IN ('Northwind', 'pubs', 'AdventureWorks', 'AdventureWorksDW')   -- Sample databases
GROUP BY    db.name

Legacy Comments
Remote DBA
2009-02-12		 re: Get all your databases and their sizes
great, thank you. How about script about table space distribution? :)
Dave Satz
2009-02-20		 re: Get all your databases and their sizes
there is a typo - this worked for me in 2008:
SELECT @@SERVERNAME AS SqlServerInstance,
db.name AS DatabaseName,
SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE af.size / 128.0E END) AS DatabaseSize,
SUM(CASE WHEN af.groupid = 0 THEN af.size / 128.0E ELSE 0 END) AS LogSize,
SUM(af.size / 128.0E) AS TotalSize
FROM master.sys.databases AS db
INNER JOIN master.sys.sysaltfiles AS af ON af.dbid = db.database_id
WHERE db.name NOT IN ('distribution', 'Resource', 'master', 'tempdb', 'model', 'msdb') -- System databases
AND db.name NOT IN ('Northwind', 'pubs', 'AdventureWorks', 'AdventureWorksDW') -- Sample databases
GROUP BY db.name
ORDER BY db.name
Peso
2009-03-12		 re: Get all your databases and their sizes
It's not a typo. The FROM and INNER JOIN parts are reading from compatibility views in SQL Server 2005/2008 and tables in SQL Server 2000.

The script works for me with SQL Server 2000, SQL Server 2005 and SQL Server 2008 as is.