SQL Server – How to get sizes of all databases on a server

To get database size information we can use sys.master_files catalog view. This view contains a row per file of a database for all databases.

The columns of interest for retrieving database size information are:

Column Name	Description
database_id	ID of the database to which the file belongs to
type_desc	Description of file type. It can be ROWS, LOG, FILESTREAM or FULLTEXT
size	File size in number of 8KB pages

Using this information we can retrieve database sizes using below query:

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(Size)* 8) / 1024.0) AS DECIMAL(18,2) )
FROM   sys.master_files
--Uncomment if you need to query for a particular database
-- WHERE      database_id = DB_ID(‘Database Name’)
GROUP BY      GROUPING SETS
              (
                     (DB_NAME(database_id), Type_Desc),
                     (DB_NAME(database_id))
              )
ORDER BY      DB_NAME(database_id), Type_Desc DESC
GO

Result Set:
 Database Name        Type                 Size in MB
AdventureWorks2012   Data File(s)         189.00
AdventureWorks2012   Log File(s)          0.75
AdventureWorks2012   NULL                 189.75
Credit               Data File(s)         170.94
Credit               Log File(s)          10.00
Credit               NULL                 180.94
master              Data File(s)          4.00
master              Log File(s)           0.75
master              NULL                  4.75
model                Data File(s)         3.06
model                Log File(s)          0.75
model                NULL                 3.81
msdb                 Data File(s)         16.69
msdb                 Log File(s)          19.63
msdb                 NULL                 36.31
Northwind            Data File(s)         4.25
Northwind            Log File(s)          3.06
Northwind            NULL                 7.31
pubs                 Data File(s)         3.25
pubs                 Log File(s)          3.06
pubs                 NULL                 6.31
SqlAndMe             Data File(s)         137.88
SqlAndMe             Log File(s)          19.13
SqlAndMe             NULL                 157.00
tempdb              Data File(s)          8.00
tempdb              Log File(s)           0.50
tempdb              NULL                  8.50
(27 row(s) affected)

The above query gets sizes for Data Files and Log Files and displays a total using GROUPING SETS.

Source: https://sqlandme.com/2013/06/10/sql-server-how-to-get-sizes-of-all-databases-on-a-server/