Using sys.master_files catalog view we can get information of 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 description of file type like ROWS,LOG,FILESTREAM or FULLTEXT and third size which is file size in 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]
-- We can query for particular database by uncomment following line
-- WHERE database_id = DB_ID('Database Name')
GROUP BY GROUPING SETS
ORDER BY DB_NAME(database_id), Type_Desc DESC