List table check constraints in Oracle database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-02-05

Table of Contents:


    Query below lists:

    (A) all check constraints on a specific table accessible to the current user in Oracle database

    (B) all check constraints on a specific tables in Oracle database

    Query was executed under the Oracle12c Database version.

    Query

    A. Table accessible to the current user

    select tab.owner as schema_name,
           tab.table_name,
           con.constraint_name,
           cols.column_name,
           search_condition as constraint,
           con.status
    from sys.all_tables tab
    join sys.all_constraints con
         on tab.owner = con.owner
         and tab.table_name = con.table_name
    join sys.all_cons_columns cols
         on cols.owner = con.owner
         and cols.constraint_name = con.constraint_name
         and cols.table_name = con.table_name
    where constraint_type = 'C'
          and tab.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS',
          'LBACSYS', 'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS',
          'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM',
          'TSMSYS','WK_TEST','WKSYS', 'WKPROXY','WMSYS','XDB','APEX_040000',
          'APEX_040200', 'APEX_PUBLIC_USER', 'DIP', 'FLOWS_30000','FLOWS_FILES',
          'MDDATA', 'ORACLE_OCM', 'XS$NULL', 'SPATIAL_CSW_ADMIN_USR', 
          'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
    order by tab.owner,
             tab.table_name,
             cols.position;
    

    B. If you have privilege on dba_tables and dba_constraints

    select tab.owner as schema_name,
           tab.table_name,
           con.constraint_name,
           cols.column_name,
           search_condition as constraint,
           con.status
    from sys.dba_tables tab
    join sys.dba_constraints con
         on tab.owner = con.owner
         and tab.table_name = con.table_name
    join sys.dba_cons_columns cols
         on cols.owner = con.owner
         and cols.constraint_name = con.constraint_name
         and cols.table_name = con.table_name
    where constraint_type = 'C'
          and tab.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS',
          'LBACSYS', 'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS',
          'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM',
          'TSMSYS','WK_TEST','WKSYS', 'WKPROXY','WMSYS','XDB','APEX_040000',
          'APEX_040200', 'APEX_PUBLIC_USER', 'DIP', 'FLOWS_30000','FLOWS_FILES',
          'MDDATA', 'ORACLE_OCM', 'XS$NULL', 'SPATIAL_CSW_ADMIN_USR', 
          'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
    order by tab.owner,
             tab.table_name,
             cols.position;
    

    Columns

    • schema_name - schema name
    • table_name - name of the table
    • constraint_name - name of the constraint
    • column_name - name of the column constraint is on
    • constraint - text of the check constraint
    • status - status of the constraint (ENABLED/DISABLED)

    Rows

    • One row represents one check constraint and its column in a database
    • Scope of rows: (A) all check constraints with columns on a table accessible to the current user in Oracle database, (B) all check constraints with columns on a table in Oracle database
    • Ordered by schema name and table name and column position

    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