How to get sizes of all databases files in SQL Server

Using sys.master_files catalog view we can get information about the size of database files.

For all databases, this view contains a row per file of database.

We will try to get column database_id which is ID of the database to which file belongs to, second column type_desc which is the description of file type like ROWS, LOG, FILESTREAM or FULLTEXT, and third size which is the file size in the number of 8kb pages.

We can retrieve database sizes using below query:

SELECT DB_NAME(database_id) AS  [BD Name],         (CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'                      WHEN Type_Desc = 'LOG'  THEN 'Log File(s)'                      ELSE Type_Desc END) AS [File Des] ,         CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) ) AS [Size in MB] FROM   sys.master_files -- We can query for particular database by uncomment following line -- 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