How to get the sizes of all databases on a SQL Server

As a SQL Server Developer, sometimes you may need to see the database size information of all databases in SQL Server. In this situation you can use sys.master_files.

It contains a row per file of a database for all databases.

Following T- SQL query returns the database size information for all database available in SQL Server.

SELECT DB_NAME(database_id) AS DataBaseName,
CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'
WHEN Type_Desc = 'LOG' THEN 'Log File(s)'
ELSE Type_Desc END AS FileType,
CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) )AS TotalSizeInMB
FROM sys.master_files
GROUP BY GROUPING SETS
(
(DB_NAME(database_id), Type_Desc),
(DB_NAME(database_id))
)
ORDER BY DB_NAME(database_id), Type_Desc DESC
GO

In case, If you want to see size of any particular database then you can modify above query as shown below.

SELECT DB_NAME(database_id) AS DataBaseName,
CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'
WHEN Type_Desc = 'LOG' THEN 'Log File(s)' 
ELSE Type_Desc END AS FileType, 
CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) )AS TotalSizeInMB 
FROM sys.master_files 
WHERE database_id = DB_ID('SqlTutorials')
GROUP BY GROUPING SETS 
(
 (DB_NAME(database_id), Type_Desc), 
 (DB_NAME(database_id)) 
) 
ORDER BY DB_NAME(database_id), Type_Desc DESC
GO