List table check constraints in Teradata database

Query below lists table check constraints.

Query

SELECT *
FROM    (
        SELECT  DatabaseName,
                TableName,
                'Column' as ConstraintLevel,
                ColumnName as ConstraintName,
                ColumnConstraint as Text
        FROM    DBC.ColumnsV
        WHERE   ColumnConstraint is not null
        UNION ALL
        SELECT  DatabaseName,
                TableName,
                'Table' as ConstraintLevel,
                ConstraintName,
                ConstraintText as Text
        FROM    DBC.Table_LevelConstraintsV
        ) AS W
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,
            ConstraintName;

Columns

  • DatabaseName - name of constraint database
  • TableName - name of the table constraint is on
  • ConstraintLevel -
    • 'Table' if constraint is table-level constraint,
    • 'Column' if constraint is column-level constraint
  • ConstraintName - name of constraint for table-level constraints and name of column for column-level constraint:
  • Text - full constraint definition

Rows

  • One row represents one check constraint or column.
  • Scope of rows: query returns all check constraints in the database
  • Ordered by database name, table name and constraint/column name

Sample results