Oracle PL/SQL/System Tables Views/user cons columns

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.ruPILE_SCHEMA_T  1 OBJECT_NAME
            MP_PK
            COMPILE_SCHEMA_TMP.ruPILE_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> --

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>