SQL Script to list all DBs Sizes in MBs

SQL Script to list all DBs Sizes in MBs

 
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