List idexes by their size in Azure SQL

Article for: SQL Server IBM Db2 Teradata MySQL MariaDB

Query below returns list indexes and their size.


select as [index_name],
    ind.type_desc as index_type,
    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,
    cast(sum(spc.data_pages * 8)/1024.00 as numeric(36,2)) as data_space_mb,
    schema_name(obj.schema_id) + '.' + as object_name,
    obj.type_desc as type
from sys.indexes ind
join sys.objects obj 
        on obj.object_id = ind.object_id
        and obj.type in ('U','V')
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
where ind.index_id > 0
group by obj.schema_id,,, ind.type_desc, 
         ind.is_unique, ind.is_primary_key, ind.is_unique_constraint, 
order by sum(spc.total_pages) desc;


  • index_name - index name
  • index_type -
  • used_mb - size of space used by index
  • allocated_mb - size of space allocated or reserved by table
  • data_space_mb - size of space used by index data
  • is_unique - indicate if index is unique
    • 1 - unique
    • 0 - not unique
  • is_primary_key indicate if index is primary key
    • 1 - primary key
    • 0 - not primary key
  • is_unique_constraint - indicate if index was created by UNIQUE constraint
  • object_name - indexed table/view name
  • object_type -
    • VIEW


  • One row represents one index in the database
  • Scope of rows: all indexes in the database
  • Ordered by index allocated space

Sample results

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.