title: Queries to generate a Disk Usage by Top Tables report in SQL Azure?
body: <p>I like using the SQL Management Studio report 'Disk Usage by Top Tables' to see what's taking up space in my databases. What equivalent query can I use in SQL Azure to see</p>
  
  <ul>
  <li>number of rows per table </li>
  <li>disk usage per table (preferably breaking out data and index)</li>
  </ul>
  
  <p>In SQL Profiler I can see that the Disk Usage by Top Tables report normally runs this: </p>
  
  <pre><code>begin try 
  
      SELECT TOP 1000
          (row_number() over(order by (a1.reserved + ISNULL(a4.reserved,0)) desc))%2 as l1,
          a3.name AS [schemaname],
          a2.name AS [tablename],
          a1.rows as row_count,
          (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
          a1.data * 8 AS data,
          (CASE WHEN (a1.used + ISNULL(a4.used,0)) &gt; a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
          (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) &gt; a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
  
      FROM    (   SELECT
                  ps.object_id,
                  SUM ( CASE WHEN (ps.index_id &lt; 2) THEN row_count    ELSE 0 END ) AS [rows],
                  SUM (ps.reserved_page_count) AS reserved,
                  SUM (CASE   WHEN (ps.index_id &lt; 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                              ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END
                      ) AS data,
                  SUM (ps.used_page_count) AS used
                  FROM sys.dm_db_partition_stats ps
                  GROUP BY ps.object_id
              ) AS a1
  
      LEFT OUTER JOIN (   SELECT
                          it.parent_id,
                          SUM(ps.reserved_page_count) AS reserved,
                          SUM(ps.used_page_count) AS used
                          FROM sys.dm_db_partition_stats ps
                          INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
                          WHERE it.internal_type IN (202,204)
                          GROUP BY it.parent_id
                      ) AS a4 ON (a4.parent_id = a1.object_id)
  
      INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )
  
      INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
  
      WHERE a2.type &lt;&gt; N'S' and a2.type &lt;&gt; N'IT'
  
  end try
  begin catch
      select
      -100 as l1
      ,   1 as schemaname
      ,       ERROR_NUMBER() as tablename
      ,       ERROR_SEVERITY() as row_count
      ,       ERROR_STATE() as reserved
      ,       ERROR_MESSAGE() as data
      ,       1 as index_size
      ,       1 as unused
  end catch
  </code></pre>
  
  <p>but that fails when running against SQL Azure with message</p>
  
  <pre><code>Msg 208, Level 16, State 1, Line 4
  Invalid object name 'sys.internal_tables'.
  </code></pre>