Query below lists table indexes.

Query

SELECT  IND.DatabaseName,
        CASE TAB.TableKind
             WHEN 'I' THEN 'Join index'
             WHEN 'N' THEN 'Hash index'
             WHEN 'T' THEN 'Table'
             WHEN 'V' THEN 'View'
             WHEN 'O' THEN 'NoPI Table'
             WHEN 'Q' THEN 'Queue table'
        END AS ObjectType,
        IND.TableName AS ObjectName,
        IND.IndexNumber,
        CASE IND.IndexType
             WHEN 'P' THEN 'Nonpartitioned primary index'
             WHEN 'Q' THEN 'Partitioned primary index'
             WHEN 'A' THEN 'Primary AMP index'
             WHEN 'S' THEN 'Secondary index'
             WHEN 'J' THEN 'Join index'
             WHEN 'N' THEN 'Hash index'
             WHEN 'K' THEN 'Primary key'
             WHEN 'U' THEN 'Unique constraint'
             WHEN 'V' THEN 'Value-ordered secondary index'
             WHEN 'H' THEN 'Hash-ordered ALL covering secondary index'
             WHEN 'O' THEN 'Valued-ordered ALL covering secondary index'
             WHEN 'I' THEN 'Ordering column of a composite secondary index'
             WHEN 'G' THEN 'Geospatial nonunique secondary index'
             END as IndexType,
        TRIM    (TRAILING ',' FROM
                XMLAGG(IND.ColumnName || ','
                ORDER BY IND.ColumnPosition)(varchar(250))) as Columns,
        CASE WHEN IND.UniqueFlag = 'Y' THEN 'Unique'
             ELSE 'Not Unique'
             END AS Uniqueness
FROM    DBC.IndicesV IND
JOIN DBC.TablesV TAB
ON IND.DatabaseName = TAB.DatabaseName
AND IND.TableName = TAB.TableName
WHERE   IND.DatabaseName NOT IN ('All', 'Crashdumps', 'DBC', 'dbcmngr', 
        'Default', 'External_AP', 'EXTUSER', 'LockLogShredder', 'PUBLIC',
        'Sys_Calendar', 'SysAdmin', 'SYSBAR', 'SYSJDBC', 'SYSLIB', 
        'SystemFe', 'SYSUDTLIB', 'SYSUIF', 'TD_SERVER_DB', 'TDStats',
        'TD_SYSGPL', 'TD_SYSXML', 'TDMaps', 'TDPUSER', 'TDQCD',
        'tdwm', 'SQLJ', 'TD_SYSFNLIB', 'SYSSPATIAL')
GROUP BY    IND.DatabaseName,
            IND.TableName,
            IND.IndexNumber,
            IND.IndexType,
            IND.UniqueFlag,
            TAB.TableKind
ORDER BY    IND.DatabaseName,
            IND.TableName,
            IND.IndexNumber;

Columns

• DatabaseName - name of database
• ObjectType - type of object:
  • Join index
  • Hash index
  • Table
  • View
  • NoPI Table
  • Queue table
• ObjectName - name of table index is defined for
• IndexNumber - id of index (unique in table)
  • 1 - Primary Indexes
  • Multiple of 4 - Secondary Indexes
• IndexType - index type:
  • Nonpartitioned primary index
  • Partitioned primary index
  • Primary AMP index
  • Secondary index
  • Join index
  • Hash index
  • Primary key
  • Unique constraint
  • Value-ordered secondary index
  • Hash-ordered ALL covering secondary index
  • Valued-ordered ALL covering secondary index
  • Ordering column of a composite secondary index
  • Geospatial nonunique secondary index
• Columns - list of index columns separated with ","
• Uniqueness - indicate if Index is unique or not

Rows

• One row represents one index
• Scope of rows: all indexes (unique and non unique) in databases
• Ordered by database name, table name, index number

Sample results