Query below returns list of indexes in a database with space they use ordered from the ones using most.

Note

Query list only indexes of tables on which was executed COLLECT DEMOGRAPHICS statement and it's only approximation by average index row size.

COLLECT DEMOGRAPHICS Example

COLLECT DEMOGRAPHICS
FOR Sales.employees -- your database name and table name
INTO TDQCD ALL;

Query

SELECT DatabaseName,
       TableName,
       SubTableType as IndexType,
       SUM(RowCount*AvgRowSize)/1024.00 as IndexSize
FROM TDQCD.DataDemographics
GROUP BY 1,2,3, SubTableId
ORDER BY IndexSize DESC;

Columns

• DatabaseName - schema name
• TableName - table name on which index is defined
• index_size - space used by index on disk in kilobytes

Rows

• One row represents one index in a database
• Scope of rows: all indexes on which demographics was collected in a database
• Ordered by index used size, from largest to smallest

Sample results