List 10 largest tables in SQL Server

Bart Gawrych - Dataedo Team Bart Gawrych 2019-02-18

Table of Contents:


    Query below list ten largest tables in database.

    Query

    select top 10 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
    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 schema_name(tab.schema_id) + '.' + tab.name
    order by sum(spc.used_pages) desc;
    

    Columns

    • table - table name with schema name
    • used_mb - size of space actually in use by table in MB
    • allocated_mb - size of allocated or reserved space by this table in MB

    Rows

    • One row represents one table
    • Scope of rows: ten tables which uses most space
    • Ordered by actually used space

    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 (updated 28-04-2020).
    Accept