get_all_database_sizes.sql

-- Get sizes of all databases and store into a temp table.
SELECT 
    DB_NAME(database_id) AS [database_name],
	CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2)) AS [data_size_mb],
	--CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 / 1024 AS DECIMAL(8,2)) AS [data_size_gb],
    CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2)) AS [log_size_mb],
	--CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 / 1024 AS DECIMAL(8,2)) AS [log_size_gb],
    CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2)) AS [total_size_mb]
	--,CAST(SUM(size) * 8. / 1024 / 1024 AS DECIMAL(8,2)) AS [total_size_gb]
FROM sys.master_files WITH(NOWAIT)
GROUP BY database_id
ORDER BY data_size_mb DESC

https://peter-whyte.com/get-database-sizes-in-sql-server/