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) + '.' + tab.name 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, tab.name 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