SQL Server 2008: How to query all databases sizes?

Question

I have MS SQL 2008 R2, 500 databases. What is the most efficient, easiest and 'modern' way to query all databases sizes.

The output should have columns:

• DatabaseName
• DataFilesSize
• LogFilesSize

Answer 1

with fs
as
(
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
)
select 
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db