Query below lists all table constraints - primary keys, unique key constraints and indexes, foreign keys and check and default constraints.

Query

SELECT  *
FROM    (
        SELECT  DatabaseName,
                TableName,
                CASE WHEN IndexType IN ('U','P','Q')
                     THEN 'Unique'
                     WHEN IndexType IN ('K')
                     THEN 'Primary Key'
                     END (VARCHAR(30)) AS ConstraintType,
                TRIM(TRAILING ',' 
                    FROM XMLAGG(ColumnName || ','
                    ORDER BY ColumnPosition)(VARCHAR(255))) AS Details
        FROM    DBC.IndicesV
        WHERE UniqueFlag = 'Y' AND IndexType IN ('K','U','P','Q')
        GROUP BY    DatabaseName,
                    TableName,
                    IndexType,
                    IndexNumber

        UNION ALL

        SELECT  ChildDB,
                ChildTable,
                'Foreign Key',
                '>- ' || ParentDB || '.' || ParentTable
        FROM    DBC.RI_Distinct_ChildrenV

        UNION ALL

        SELECT  DatabaseName,
                TableName,
                'Column Constraint',
                REGEXP_SUBSTR(ColumnConstraint,'\(.*',1,1)
        FROM    DBC.ColumnsV
        WHERE   ColumnConstraint IS NOT NULL

        UNION ALL

        SELECT  DatabaseName,
                TableName,
                'Table Constraint',
                REGEXP_SUBSTR(ConstraintText,'\(.*',1,1)
        FROM    DBC.Table_LevelConstraintsV

        UNION ALL

        SELECT  COL.DatabaseName,
                COL.TableName,
                'Default',
                COL.ColumnName || ' = ' || COL.DefaultValue
        FROM    DBC.ColumnsV COL
        JOIN    DBC.Tablesv TAB
        ON      TAB.DatabaseName = COL.DatabaseName
        AND     TAB.TableName = COL.TableName
        AND     TAB.TableKind = 'T'
        WHERE     COL.DefaultValue IS NOT NULL
        ) AS C
WHERE   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')
ORDER BY    DatabaseName,
            TableName,
            ConstraintType;

Columns

• DatabaseName - database name
• TableName - table name:
  • Table
  • View
• ConstraintType - type of constraint:
  • Primary key
  • Foregin key
  • Unique
  • Table Constraint - table-level check constraint
  • Column Constraint - column-level check constraint
  • Default
• Details - details of this constraint:
  • Primary key - PK column(s)
  • Unique key - UK column(s)
  • Foregin key - parent table name
  • Check constraint - check definition
  • Default constraint - column name and default value definition

Rows

• One row represents one constraint: PK, UK, FK, Check, Default
• Scope of rows: all constraints
• Ordered by schema name, table name

Sample results