Table of Contents:


    Follow us

    List of tables by their size in Azure SQL Database

    Rene Castro - Dataedo Team Rene Castro 2018-12-10 2019-04-01
    This applies to Azure SQL Database. Article available also for: SQL Server MySQL PostgreSQL MariaDB IBM Db2

    The query below returns tables in a database with the space they use, sorted by those that use more.

    Query

    select schema_name(tab.schema_id) + '.' + tab.name as [table],
        cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_mb,
        cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_mb
    from sys.tables tab
        inner join sys.indexes ind 
            on tab.object_id = ind.object_id
        inner join sys.partitions part 
            on ind.object_id = part.object_id and ind.index_id = part.index_id
        inner join sys.allocation_units spc
            on part.partition_id = spc.container_id
    group by schema_name(tab.schema_id) + '.' + tab.name
    order by sum(spc.used_pages) desc
    

    Columns

    • table - table name with schema name
    • used_mb - space used in MB
    • allocated_mb - space allocated in MB

    Rows

    • One row: represents one table in a database
    • Scope of rows: all tables in a database
    • Ordered by: table used size, from largest to smallest

    Sample results

    Tables in the AdventureWorksLT database, sorted from the ones that use more space to the ones that use less.

    Share this article

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