Combine user_constraints and user_cons_columns table
SQL>
SQL> set echo off
SQL>
SQL> set lines 150
SQL> set pages 100
SQL> set feedback on
SQL>
SQL> column cname format a35 heading 'Constraint'
SQL> column ctype format a11 heading 'Type'
SQL> column crule format a40 heading 'Constraint Rule'
SQL> column colname format a20 heading 'Column'
SQL> column cpos format 9 heading '#'
SQL> column crefer format a20 heading 'Refers To'
SQL> column csort noprint
SQL>
SQL> break on ctype skip 2
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 TEMP_EMP.SYS_C005563 LASTNAME "LASTNAME" IS NOT NULL
TEMP_EMP.SYS_C005564 FIRSTNAME "FIRSTNAME" IS NOT NULL
MYTABLE_SESSION.SYS_C005719 EMPNO "EMPNO" IS NOT NULL
AQ$_EMP_CHANGES_S.SYS_C005785 SUBSCRIBER_ID "SUBSCRIBER_ID" IS NOT NULL
AQ$_EMP_CHANGES_S.SYS_C005786 QUEUE_NAME "QUEUE_NAME" IS NOT NULL
EMP_REG.SYS_C005824 EMPNO "EMPNO" IS NOT NULL
Primary Key COMPILE_SCHEMA_TMP.COMPILE_SCHEMA_T 1 OBJECT_NAME
MP_PK
COMPILE_SCHEMA_TMP.COMPILE_SCHEMA_T 2 OBJECT_TYPE
MP_PK
EMP_CHANGES.SYS_C005784 1 MSGID
AQ$_EMP_CHANGES_S.SYS_C005787 1 SUBSCRIBER_ID
DEPT_AND_EMP.SYS_C005826 1 DEPTNO
UPPER_ENAME.SYS_IOT_TOP_15816 1 X$ENAME
UPPER_ENAME.SYS_IOT_TOP_15816 2 X$RID
AQ$_EMP_CHANGES_T.SYS_IOT_TOP_16247 1 NEXT_DATE
AQ$_EMP_CHANGES_T.SYS_IOT_TOP_16247 2 TXN_ID
AQ$_EMP_CHANGES_T.SYS_IOT_TOP_16247 3 MSGID
AQ$_EMP_CHANGES_H.SYS_IOT_TOP_16249 1 MSGID
AQ$_EMP_CHANGES_H.SYS_IOT_TOP_16249 2 SUBSCRIBER#
AQ$_EMP_CHANGES_H.SYS_IOT_TOP_16249 3 NAME
AQ$_EMP_CHANGES_H.SYS_IOT_TOP_16249 4 ADDRESS#
AQ$_EMP_CHANGES_G.SYS_IOT_TOP_16251 1 MSGID
AQ$_EMP_CHANGES_G.SYS_IOT_TOP_16251 2 SUBSCRIBER#
AQ$_EMP_CHANGES_G.SYS_IOT_TOP_16251 3 NAME
AQ$_EMP_CHANGES_G.SYS_IOT_TOP_16251 4 ADDRESS#
AQ$_EMP_CHANGES_I.SYS_IOT_TOP_16254 1 SUBSCRIBER#
AQ$_EMP_CHANGES_I.SYS_IOT_TOP_16254 2 NAME
AQ$_EMP_CHANGES_I.SYS_IOT_TOP_16254 3 QUEUE#
AQ$_EMP_CHANGES_I.SYS_IOT_TOP_16254 4 MSG_PRIORITY
AQ$_EMP_CHANGES_I.SYS_IOT_TOP_16254 5 MSG_ENQ_TIME
AQ$_EMP_CHANGES_I.SYS_IOT_TOP_16254 6 MSG_STEP_NO
AQ$_EMP_CHANGES_I.SYS_IOT_TOP_16254 7 MSG_CHAIN_NO
AQ$_EMP_CHANGES_I.SYS_IOT_TOP_16254 8 MSG_LOCAL_ORDER_NO
AQ$_EMP_CHANGES_I.SYS_IOT_TOP_16254 9 MSGID
Other DEPT_AND_EMP.SYS_C005827 1 SYS_NC0000400005$
34 rows selected.
SQL>
SQL>
SQL>
SQL> --
Related examples in the same category