List tables by the size of their indexes in SQL Server

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


select schema_name(tab.schema_id) + '.' + 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,
order by used_space desc;


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

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.