List tables by their size in Azure SQL Database

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.

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.