List table check constraints in Db2 database

Query below lists table check constraints.

Query

select con.tabschema concat '.' concat con.tabname as table,
    col.colname as column_name,
    con.text  as definition,
    case tab.enforced 
      when 'N' then 'disabled'
      when 'Y' then 'active'
    end as status,
    con.constname as constraint_name
from syscat.checks con
join syscat.colchecks col on col.constname = con.constname 
        and col.tabschema = con.tabschema 
        and col.tabname = con.tabname
join syscat.tabconst tab on tab.constname = con.constname 
        and tab.tabschema = con.tabschema 
        and tab.tabname = con.tabname
where con.tabschema not like 'SYS%'
order by con.tabschema concat '.' concat con.tabname,
    col.colname

Columns

  • table - schema and table name
  • column_name - name of the column
  • definition - SQL expression that defines this check constraint
  • status - constraint status:
    • 'active' if constraint is active,
    • 'disabled' for disabled constraints
  • constraint_name - name of the constraint in the database

Rows

  • One row represents one check constraint or column. If check constraint consists of multiple columns (table-level check constraints), each column appears separately
  • Scope of rows: query returns all check constraints in the database
  • Ordered by table schema and name, column name

Sample results

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