Query below returns list of indexes in a database with space they use ordered from the ones using most.
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;
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;
- DatabaseName - schema name
- TableName - table name on which index is defined
- index_size - space used by index on disk in kilobytes
- 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