List table check constraints in Oracle database

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.