SQL Query to List all Database Sizes

Somebody recently asked to have a SQL query to show a list of database sizes ordered by biggest first. Here is a query which does just that;

WITH RH
AS
(
    SELECT database_id, type, SIZE * 8.0 / 1024 SIZE
    FROM sys.master_files
)

SELECT
NAME as [DB Name], 
(SELECT SUM(SIZE) / 1024 FROM RH WHERE TYPE = 0 and RH.database_id = DB.database_id) [Data File in GB],
(SELECT SUM(SIZE) / 1024 FROM RH WHERE TYPE = 1 and RH.database_id = DB.database_id) [Log File in GB]

   
FROM sys.databases db

ORDER BY
(SELECT SUM(SIZE) / 1024 FROM RH WHERE TYPE = 0 and RH.database_id = DB.database_id) DESC

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s