Get sizes of all databases on a server

Sys.master_files system view is having the all information about the database in the server. This view is under MSDB database.

  

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:

Use below sql script to get the database size

SELECT [Database Name],       cast(DataFile as decimal(10,2))               AS [Data File(s)],       cast(LogFile as decimal(10,2))                AS [Log File(s)],       cast((DataFile + LogFile) as decimal(10,2))   AS [Total size(Data + Log)] FROM   (SELECT DB_NAME(Database_id) AS [Database Name],                size * 8.0 / 1024    AS SizeInMB,                CASE                  WHEN TYPE = 0 THEN 'DataFile'                  ELSE 'LogFile'                END                  AS FileType         FROM   sys.master_files) D        PIVOT ( MAX(SizeInMB)              FOR FileType IN (DataFile,              LogFile)) FinalTable