List 10 largest tables in Azure SQL database

Query below list ten largest tables in database.


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


  • 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


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

Sample results

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