Query below lists all unique indexes in the database.

Check out also list of unique constraints.

Query

select ind.indname as index_name,
       ind.tabschema concat '.' concat ind.tabname as table,
       ind.iid as index_id,
       case ind.uniquerule 
           when 'P' then 'Primary key'
           when 'U' then 'Unique'
       end as type,
       listagg(cols.colname, ', ')
            within group (order by cols.colname) as columns,
    case ind.indextype
        when 'BLOK' then 'Block index'
        when 'REG' then 'Regular index'
        when 'CPMA' then 'Page map index for a column-organized table'
        when 'RCT ' then 'Key sequence index for a range-clustered table'
        when 'CLUS' then 'Clustering index'
        when 'TEXT' then 'Text index'
        when 'DIM' then 'Dimension block index'
        when 'XPTH' then 'XML path index'
        when 'XRGN' then 'XML region index'
        when 'XVIL' then 'Index over XML column (logical)'
        when 'XVIP' then 'Index over XML column (physical)'
        end as index_type
from syscat.indexes ind
join syscat.indexcoluse cols
     on ind.indname = cols.indname
     and ind.indschema = cols.indschema
where ind.tabschema not like 'SYS%'
      and ind.uniquerule != 'D'
group by ind.indname,
         ind.tabschema,
         ind.iid,
         ind.uniquerule,
         ind.indextype,
         ind.tabname
order by table,
         index_id;

Columns

• index_name - index name
• table - table schema and name
• index_id - id of index (unique in table)
• type
  • PRIMARY KEY
  • UNIQUE
• columns - list of index columns separated with ","
• index_type :
  • Block index
  • Regular index
  • Page map index for a column-organized table
  • Key sequence index for a range-clustered table
  • Clustering index
  • Text index
  • Dimension block index
  • XML path index
  • XML region index
  • Index over XML column (logical)
  • Index over XML column (physical)

Rows

• One row represents one unique index
• Scope of rows: all unique indexes in the database
• Ordered by table schema, table name and index id

Sample results