Queries to generate a Disk Usage by Top Tables report in SQL Azure?

Question

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

• number of rows per table
• disk usage per table (preferably breaking out data and index)

In SQL Profiler I can see that the Disk Usage by Top Tables report normally runs this:

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)) > 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)) > 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 < 2) THEN row_count    ELSE 0 END ) AS [rows],
                SUM (ps.reserved_page_count) AS reserved,
                SUM (CASE   WHEN (ps.index_id < 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 <> N'S' and a2.type <> 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

but that fails when running against SQL Azure with message

Msg 208, Level 16, State 1, Line 4
Invalid object name 'sys.internal_tables'.

Answer 1

----USE THIS TO FIND TOP DATA TABLE

SELECT DISTINCT A.NAME,B.row_count FROM SYS.tables A JOIN sys.dm_db_partition_stats B
ON A.object_id = B.object_id
ORDER BY 2 DESC

Answer 2

Here is one that is functional on SQL 2012 that has basically the same data as the Report. Based on a previous answer and expanded a little.

It's wrapped in a SP to allow it to be called against any DB on the server so you don't need 20 copies of it.

Comment out whichever unit size you don't want. GB was useful to me.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.TableSizes

    @DB varchar(50) = 'OP'
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
   
    -- DECLARE @DB AS varchar(50)='OP'; -- DEBUG LINE

    DECLARE @sql nvarchar(max)='
    WITH A AS (
        SELECT Sc.name AS SchemaName, O.name AS TableName
            , SUM(ps.row_count) AS TotalRows
            , CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 / 1024 AS Decimal(28,6)) AS TotalSizeGB
            , CAST(SUM(ps.used_page_count) * 8.0 / 1024 / 1024 AS Decimal(28,6)) AS UsedTotalSizeGB
            , CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS Decimal(28,3)) AS TotalSizeMB
            , CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS Decimal(28,3)) AS UsedTotalSizeMB
            , CAST(SUM(ps.reserved_page_count) * 8.0 AS Decimal(28,0)) AS TotalSizeKB
            , CAST(SUM(ps.used_page_count) * 8.0 AS Decimal(28,0)) AS UsedTotalSizeKB
            --, O.Type
        FROM $DB$.sys.dm_db_partition_stats AS PS
            JOIN $DB$.sys.objects AS O ON PS.object_id=O.object_id
            JOIN $DB$.sys.schemas AS Sc on O.schema_id=Sc.Schema_id
        WHERE O.Type=''U'' -- user tables only
        GROUP BY Sc.name, O.name
            --, O.Type
    )
    SELECT A.*
        , CASE WHEN A.TotalSizeGB=0 THEN NULL ELSE 100.0 * A.UsedTotalSizeGB / A.TotalSizeGB END AS UsedPercent
    FROM A
    ORDER BY A.TotalSizeGB DESC
    '
    SET @sql=replace(@sql,'$DB$',@DB);

    EXEC sp_executesql @sql;

    -- EXEC Agent.TableSizes 'Scorecards' -- DEBUG LINE

END
GO

Answer 3

try querying sys.dm_db_partition_stats

e.g.,

-- Calculates the size of individual database objects.

SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024 as Size
FROM sys.dm_db_partition_stats, sys.objects 
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id 
GROUP BY sys.objects.name
ORDER BY Size DESC;
GO

Answer 4

EDIT 24 May 2016: Doesn't seem to work these days ... I'll have to rework this

Well I guess since it's sys.internal_tables that's the problem the simplest thing is to remove it from the mix. A slight adjustment and we're left with this:

 SELECT TOP 1000
        a3.name AS SchemaName,
        a2.name AS TableName,
        a1.rows as Row_Count,
        (a1.reserved )* 8.0 / 1024 AS reserved_mb,
        a1.data * 8.0 / 1024 AS data_mb,
        (CASE WHEN (a1.used ) > a1.data THEN (a1.used ) - a1.data ELSE 0 END) * 8.0 / 1024 AS index_size_mb,
        (CASE WHEN (a1.reserved ) > a1.used THEN (a1.reserved ) - a1.used ELSE 0 END) * 8.0 / 1024 AS unused_mb

    FROM    (   SELECT
                ps.object_id,
                SUM ( CASE WHEN (ps.index_id < 2) THEN row_count    ELSE 0 END ) AS [rows],
                SUM (ps.reserved_page_count) AS reserved,
                SUM (CASE   WHEN (ps.index_id < 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

    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 <> N'S' and a2.type <> N'IT'   
    order by a1.data desc         

There are some interesting management views and functions provided by Azure but not needed in this case ... I think.