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