Cacher is the code snippet organizer for pro developers

WITH   fs  
 AS  
 (  
    SELECT database_id,   TYPE   ,   SIZE    *   8.0   /   1024 size_in_mb,   SIZE    *   8.0   /   1024   /   1024 size_in_gb, Physical_Name  
    from sys.master_files  
 )  
 SELECT   
       NAME   ,state_desc,  
  (select   SUM   (   CAST   (round(size_in_mb,2)   AS    DECIMAL   (8,2))) from fs where   TYPE    =   0   AND   fs.database_id   =   db.database_id) Data_File_Size_In_MB,  
    (select   SUM   (   CAST   (round(size_in_mb,2)   AS    DECIMAL   (8,2))) from fs where   TYPE    =   1   AND   fs.database_id   =   db.database_id) Log_File_Size_In_MB,  
  (select   SUM   (   CAST   (round(size_in_gb,2)   AS    DECIMAL   (8,2))) from fs where   TYPE    =   0   AND   fs.database_id   =   db.database_id) Data_File_Size_In_GB,  
    (select   SUM   (   CAST   (round(size_in_gb,2)   AS    DECIMAL   (8,2))) from fs where   TYPE    =   1   AND   fs.database_id   =   db.database_id) Log_File_Size_In_GB,  
  (select   MAX   (Physical_Name) from fs where   TYPE    =   1   AND   fs.database_id   =   db.database_id) File_FullPath  
 FROM   sys.databases db  
 ORDER BY   Data_File_Size_In_GB   DESC