Query To Find All Databases Sizes

If you’re using a GUI tool, such as SSMS to manage your databases, you can easily check the size of your database by clicking your way through the GUI (right-click the database, point to Reports, then Standard Reports, and then click Disk Usage).

However, if you prefer to use T-SQL to manage your databases, you’ll need to run a query to find all databases sizes.


USE master
GO

WITH fs
AS
(
SELECT database_id, type, size * 8.0 / 1024 size
FROM sys.master_files
)
SELECT
name AS DatabaseName,
(SELECT sum(size) FROM fs WHERE type = 0 AND fs.database_id = db.database_id) DataFileSizeMB,
(SELECT sum(size) FROM fs WHERE type = 1 AND fs.database_id = db.database_id) LogFileSizeMB
FROM sys.databases db