SQL Server 2008: How to query all databases sizes?

with fsas(    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) LogFileSizeMBfrom sys.databases db

I don't know exactly what you mean by efficiency but this is straightforward and it works for me:

SELECT    DB_NAME(db.database_id) DatabaseName,    (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,    (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,    (CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,    (CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMBFROM sys.databases db    LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id    LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id    LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id    LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id

With results like:

DatabaseName  RowSizeMB LogSizeMB StreamSizeMB TextIndexSizeMB------------- --------- --------- ------------ ---------------master        4         1.25      NULL         NULLmodel         2.25      0.75      NULL         NULLmsdb          14.75     8.1875    NULL         NULLtempdb        8         0.5       NULL         NULL

Note: was inspired by this article

Here's a simple, quick and reliable query that will give all database and log file names, sizes and also database statuses (e.g. ONLINE) in a nice, easy to read output:

SELECT    D.name,    F.Name AS FileType,    F.physical_name AS PhysicalFile,    F.state_desc AS OnlineStatus,    CAST(F.size AS bigint) * 8*1024 AS SizeInBytes,    CAST((F.size*8.0)/1024/1024 AS decimal(18,3)) AS SizeInGBFROM     sys.master_files F    INNER JOIN sys.databases D ON D.database_id = F.database_idORDER BY SizeInBytes desc