List tables by the size of their indexes in Azure SQL

Bart Gawrych - Dataedo Team Bart Gawrych 2019-04-01

Table of Contents:


    Article for: Azure SQL Database SQL Server MySQL MariaDB IBM Db2

    Query below returns tables in a database with space used by their indexes ordered from the ones using most.

    Query

    select schema_name(tab.schema_id) + '.' + tab.name as [table],
           (sum(spc.total_pages * 8) / 1024.0) as allocated_space,
           (sum(spc.used_pages * 8) / 1024.0) as used_space
    from sys.tables tab
    join sys.indexes ind
         on tab.object_id = ind.object_id
    join sys.partitions part
         on ind.object_id = part.object_id
         and ind.index_id = part.index_id
    join sys.allocation_units spc
         on part.partition_id = spc.container_id
    group by tab.schema_id, tab.name
    order by used_space desc;
    

    Columns

    • table - table's schema name and name
    • allocated_space - space allocated for indexes
    • used_space - space actually used by indexes

    Sample results

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept