SQL Server – How to query all databases sizes?

Sometimes you need to get a quick glance of the size of all the databases in your instance. Following script would give you just that. It gives you the size of the data file as well as the log file.


SELECT
 DB_NAME(db.database_id) DatabaseName,
 (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
 (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,
 (CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
 (CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB
 FROM sys.databases db
 LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id
 LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id
 LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id
 LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id

And you should end up with something like the following:

DatabaseName  RowSizeMB LogSizeMB StreamSizeMB TextIndexSizeMB
------------- --------- --------- ------------ ---------------
master        4         1.25      NULL         NULL
model         2.25      0.75      NULL         NULL
msdb          14.75     8.1875    NULL         NULL
tempdb        8         0.5       NULL         NULL