List table check constraints in Teradata database

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

Table of Contents:


    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

    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