List table indexes in Teradata database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-10-31

Table of Contents:


    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

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept