From: esquel@sommarskog.se   
      
    (rja.carnegie@gmail.com) writes:   
   > On Tuesday, 21 May 2013 14:27:33 UTC+1, n  wrote:   
   >> have MS SQL 2008 R2, 500 databases. What is the most efficient, easiest   
   and 'modern' way to query all databases sizes.   
   >>   
   >> The output should have columns:   
   >>   
   >> ?DatabaseName   
   >>   
   >> ?DataFilesSize   
   >>   
   >> ?LogFilesSize   
   >   
   > I've only got SQL Server 2005; here, the system catalog view,   
   > sys.master_files, has the information you're looking for.   
   > You'll need DB_NAME([database_id]) for the first column, and,   
   > to have the MDF/NDF and log file sizes side by side in a row,   
   > you're going to need one of those "PIVOT" bits, I suppose.   
   > I can never remember how to do that.  But it is "modern".  ;-)   
      
   Here is a query. Fine-tune it to produce the units you want the data in.   
      
   SELECT d.name,   
         SUM(CASE WHEN mf.type <> 1 THEN convert(bigint, mf.size) * 8192 END) /   
                1000000000 AS [DataFileSize in GB],   
          SUM(CASE WHEN mf.type = 1 THEN convert(bigint, mf.size) * 8192 END) /   
                1000000000 AS [LogFileSize in GB]   
   FROM   sys.databases d   
   JOIN   sys.master_files mf ON d.database_id = mf.database_id   
   GROUP  BY d.name   
   ORDER  BY d.name   
      
      
      
   --   
   Erland Sommarskog, Stockholm, esquel@sommarskog.se   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)