SQL Script to list all DBs Sizes in MBs
[code language=”sql”]
SELECT DbName
,DbState
,DbRecovery
,[ROWS], [LOG], [FILESTREAM], [FULLTEXT]
FROM (
SELECT DB.name AS DbName
,DB.state_desc AS DbState
,DB.recovery_model_desc AS DBRecovery
,MF.type_desc AS FileType
,CONVERT(int, ROUND(MF.size * 0.0078125, 0)) AS SizeMB
FROM sys.databases AS DB
INNER JOIN sys.master_files AS MF
ON DB.database_id = MF.database_id
WHERE HAS_DBACCESS(DB.name) = 1
) AS DBS
PIVOT (SUM(SizeMB)
FOR FileType IN ([ROWS], [LOG], [FILESTREAM], [FULLTEXT])
) AS PVT
ORDER BY DbName
[/code]
