Query below returns all primary keys and unique indexes on tables in Teradata database.

Check out also list of unique indexes.

Query

SELECT  DatabaseName,
        TableName,
        CASE 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 'K' THEN 'Primary key'
            WHEN 'U' THEN 'Unique Constraint'
            ELSE IndexType
            END,
        TRIM(TRAILING ',' FROM 
            (XMLAGG(ColumnName || ','
                ORDER BY ColumnPosition) (varchar(100))))
            AS Columns
FROM    DBC.IndicesV 
WHERE   UniqueFlag = 'Y'
AND 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    1,2,3
ORDER BY    DatabaseName,
            TableName;

Columns

• DatabaseName - database name
• TableName - name of table
• IndexType -
  • Nonpartitioned primary index
  • Partitioned primary index
  • Primary AMP index
  • Secondary index
  • Primary key
  • Unique Constraint
• Columns - index columns separated with ","

Rows

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

Sample results