в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T10:01:04Z

1 версия

Новая страница

==Combine user_constraints and user_cons_columns table==



<source lang="sql">

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> --

</source>

==Output contraints name, column, rule==



<source lang="sql">

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>

</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/System Tables Views/user cons columns - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия