Sizes of all databases on a SQL Server instance

dbsizes.sql

SELECT
    DB_NAME(db.database_id) DBName,
    (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSize_MB,
    (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSize_MB,
    (CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSize_MB,
    (CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSize_MB
FROM sys.databases db
    LEFT JOIN (SELECT database_id, SUM(CAST(size AS BIGINT)) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id
    LEFT JOIN (SELECT database_id, SUM(CAST(size AS BIGINT)) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id
    LEFT JOIN (SELECT database_id, SUM(CAST(size AS BIGINT)) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id
    LEFT JOIN (SELECT database_id, SUM(CAST(size AS BIGINT)) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id