Query below returns tables in a database with space used by their indexes ordered from the ones using most.

Query

select tabschema || '.' || tabname as table,
       index_object_p_size as index_size
from sysibmadm.admintabinfo
where tabschema not like 'SYS%'
order by index_size desc;

Columns

• table - table's schema name and name
• index_size - space allocated for table's indexes

Sample results