Combine user_constraints and user_cons_columns table : user_cons_columns « System Tables Views « Oracle PL / SQL






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

1.Output contraints name, column, rule