Query below returns list indexes and their size.

Query

select ind.name 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,
    ind.is_unique,
    ind.is_primary_key,
    ind.is_unique_constraint,
    schema_name(obj.schema_id) + '.' + obj.name 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, obj.name, ind.name, ind.type_desc, 
         ind.is_unique, ind.is_primary_key, ind.is_unique_constraint, 
         obj.type_desc
order by sum(spc.total_pages) desc;

Columns

• index_name - index name
• index_type -
  • CLUSTERED
  • NONCLUSTERED
• 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 -
  • USER_TABLE
  • VIEW

Rows

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

Sample results