get sql server all dbs size

sql_server_db_sizes.sql

SELECT [Database Name] = DB_NAME(database_id),

       [Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'

                     WHEN Type_Desc = 'LOG'  THEN 'Log File(s)'

                     ELSE Type_Desc END,

       [Size in MB] = CAST( ((SUM(cast(Size as bigint))* 8) / 1024.0) AS DECIMAL(20,0) )

FROM   sys.master_files



-- WHERE      database_id = DB_ID(‘Database Name’)

GROUP BY      GROUPING SETS

              (

                     (DB_NAME(database_id), Type_Desc),

                     (DB_NAME(database_id))

              )

ORDER BY     CAST( ((SUM(cast(Size as bigint))* 8) / 1024.0) AS DECIMAL(20,0) ) desc --DB_NAME(database_id), Type_Desc DESC

GO