Finding Index Details in Teradata

Finding Index Details in Teradata

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;