MS SQL 2005+

SELECT @@servername AS instance_name ,a.name AS database_name, c.data_Size_MBs AS data_size , b.log_Size_MBs AS log_size ,a.total_Size_MBs AS total_size FROM

(

SELECT d.name,

ROUND(SUM(mf.size) * 8 / 1024, 0) total_Size_MBs

FROM sys.master_files mf

INNER JOIN sys.databases d ON d.database_id = mf.database_id

WHERE d.database_id > 4 — Skip system databases

GROUP BY d.name

) AS a

INNER JOIN

(SELECT DB_NAME(mf.database_id) AS name,

ROUND(SUM(mf.size) * 8 / 1024, 0) log_Size_MBs

FROM sys.master_files mf

INNER JOIN sys.databases d ON d.database_id = mf.database_id

where mf.Physical_Name like ‘%.ldf’ and d.database_id > 4

GROUP BY d.name,mf.database_id

) AS b

ON a.name=b.name

INNER JOIN

(SELECT DB_NAME(mf.database_id) AS Name,

ROUND(SUM(mf.size) * 8 / 1024, 0) data_Size_MBs

FROM sys.master_files mf

INNER JOIN sys.databases d ON d.database_id = mf.database_id

where mf.Physical_Name like ‘%.mdf’ and d.database_id > 4

GROUP BY d.name,mf.database_id

) AS c

ON a.name=c.name

MS SQL 2000

SELECT @@servername as instance_name,a.name, data_size,log_size, log_size+data_size AS total

FROM

(SELECT sdb.name,ROUND(SUM(size) * 8 / 1024, 0) AS log_size

FROM master..sysdatabases AS sdb

INNER JOIN master..sysaltfiles AS saf

ON sdb.dbid= saf.dbid

WHERE sdb.dbid > 4 and saf.fileid =1

group by sdb.name

) AS a

INNER JOIN

(SELECT sdb.name,ROUND(SUM(size) * 8 / 1024, 0) AS data_size

FROM master..sysdatabases AS sdb

INNER JOIN master..sysaltfiles AS saf

ON sdb.dbid= saf.dbid

WHERE sdb.dbid > 4 and saf.fileid =2

group by sdb.name

)AS b

ON a.name=b.name

ORDER BY a.name