Output contraints name, column, rule
SQL>
SQL> select a.constraint_type || a.constraint_name as csort,
2 decode(a.constraint_type,'P','Primary Key','R','Foreign Key','C','Check','Other') as ctype,
3 a.table_name || '.' || a.constraint_name as cname,
4 b.position as cpos,
5 b.column_name as colname,
6 a.search_condition as crule,
7 a.r_constraint_name as crefer
8 from user_constraints a, user_cons_columns b
9 where a.constraint_name = b.constraint_name
10 order by csort, b.position
11 /
Type Constraint # Column Constraint Rule Refers To
----------- ----------------------------------- -- -------------------- ---------------------------------------- --------------------
Check WORKING_CUSTOMERS.SYS_C005236 LASTNAME "LASTNAME" IS NOT NULL
WORKING_CUSTOMERS.SYS_C005237 FIRSTNAME "FIRSTNAME" IS NOT NULL
TMP.SYS_C007377 STUDENTNAME "STUDENTNAME" IS NOT NULL
WORKING_EMPLOYEES.SYS_C008777 LASTNAME "LASTNAME" IS NOT NULL
WORKING_EMPLOYEES.SYS_C008778 FIRSTNAME "FIRSTNAME" IS NOT NULL
WORKING_EMPS.SYS_C009664 LASTNAME "LASTNAME" IS NOT NULL
WORKING_EMPS.SYS_C009665 FIRSTNAME "FIRSTNAME" IS NOT NULL
Primary Key CUST_WITH_VARRAY_TEMP_TABLE.SYS_C00 1 ID
4799
P1.SYS_C005096 1 X
IT.SYS_C005376 1 C1
CUST_NO_KEY_TABLE.SYS_C005404 1 CUST_NO
DEMO.SYS_C005536 1 X
P.SYS_C005543 1 PK
INVENTORY_TBL.SYS_C006948 1 ITEM_ID
EMPLOYEETEMPTABLE.SYS_C008156 1 ID
EMPTEMPTABLE.SYS_C009072 1 ID
IX.SYS_IOT_TOP_16135 1 X
17 rows selected.
SQL> spool off
SQL>
| 1. | Combine user_constraints and user_cons_columns table |