8. ÷ÙÂÏÒËÁ ÍÅÔÁÄÁÎÎÙÈ
ðÒÏÔÏËÏÌ ×ÙÐÏÌÎÅÎÉÑ ÚÁÐÒÏÓÏ× Ë ÓÌÏ×ÁÒÀ ÄÁÎÎÙÈ Oracle
SQL> /* ÷ÙÂÏÒËÁ ÓÐÉÓËÁ ÔÁÂÌÉÃ */
SQL> SELECT table_name
2 FROM all_tables
3 WHERE owner='U1';
TABLE_NAME
------------------------------
DEPARTMENT
EDUCATION
EDU_LEVEL
EDU_PROFILE
EMPLOYEE
JOB
STAFF
WORK
8 rows selected.
SELECT SUBSTR(table_name,1,12) AS table_name,
SUBSTR(column_name,1,12) AS column_name,
SUBSTR(data_type,1,12) AS data_type,
data_length, nullable
FROM all_tab_columns
WHERE table_name IN
(SELECT table_name
FROM all_tables
WHERE owner='U1')
ORDER BY 1;
SQL> SELECT SUBSTR(table_name,1,12) AS table_name,
2 SUBSTR(column_name,1,12) AS column_name,
3 SUBSTR(data_type,1,12) AS data_type,
4 data_length, nullable
5 FROM all_tab_columns
6 WHERE table_name IN
7 (SELECT table_name
8 FROM all_tables
9 WHERE owner='U1')
10 ORDER BY 1;
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH N
------------ ------------ ------------ ----------- -
DEPARTMENT DEP_ID NUMBER 22 N
DEPARTMENT SALARY_LIMIT NUMBER 22 Y
DEPARTMENT DEP_NAME VARCHAR2 20 Y
EDUCATION LEVEL_ID NUMBER 22 N
EDUCATION PROF_ID NUMBER 22 N
EDUCATION WHEN_YEAR NUMBER 22 Y
EDUCATION EMPL_ID NUMBER 22 N
EDU_LEVEL LEVEL_ID NUMBER 22 N
EDU_LEVEL LEVEL_NAME VARCHAR2 20 Y
EDU_PROFILE PROF_ID NUMBER 22 N
EDU_PROFILE PROF_NAME VARCHAR2 20 Y
EMPLOYEE PHONE NUMBER 22 Y
EMPLOYEE ADDRESS VARCHAR2 80 Y
EMPLOYEE BIRTH DATE 7 Y
EMPLOYEE NAME_2ND VARCHAR2 20 Y
EMPLOYEE NAME_1ST VARCHAR2 20 Y
EMPLOYEE NAME_LAST VARCHAR2 20 N
EMPLOYEE EMPL_ID NUMBER 22 N
JOB JOB_ID NUMBER 22 N
JOB MIN_SALARY NUMBER 22 Y
JOB LEVEL_ID NUMBER 22 N
JOB EXPERIENCE NUMBER 22 Y
JOB PROF_ID NUMBER 22 Y
JOB MAX_SALARY NUMBER 22 Y
JOB JOB_NAME VARCHAR2 40 Y
STAFF STAFF_ID NUMBER 22 N
STAFF JOB_ID NUMBER 22 N
STAFF RATE NUMBER 22 N
STAFF DEP_ID NUMBER 22 N
WORK EMPL_ID NUMBER 22 N
WORK RATE NUMBER 22 N
WORK MNG_FLAG CHAR 1 Y
WORK START_DATE DATE 7 N
WORK STAFF_ID NUMBER 22 N
WORK SALARY NUMBER 22 Y
35 rows selected.
SQL> /* ÷ÙÂÏÒËÁ ÓÐÉÓËÁ ×ÓÅÈ ÏÇÒÁÎÉÞÅÎÉÊ */
SQL> SELECT SUBSTR(constraint_name,1,15) AS constraint_name,
2 SUBSTR(table_name,1,12) AS table_name,
3 constraint_type,
4 SUBSTR(r_constraint_name,1,17) AS r_constraint_n
5 delete_rule
6 FROM all_constraints
7 WHERE owner='U1';
CONSTRAINT_NAME TABLE_NAME C R_CONSTRAINT_NAME DELETE_RU
--------------- ------------ - ----------------- ---------
SYS_C001259 EDU_LEVEL P
SYS_C001260 EDU_PROFILE P
SYS_C001261 EMPLOYEE C
SYS_C001262 EMPLOYEE P
SYS_C001263 DEPARTMENT P
SYS_C001264 JOB C
SYS_C001265 JOB P
SYS_C001266 JOB R SYS_C001259 CASCADE
SYS_C001267 JOB R SYS_C001260 CASCADE
SYS_C001268 EDUCATION C
SYS_C001269 EDUCATION C
SYS_C001270 EDUCATION P
SYS_C001271 EDUCATION R SYS_C001259 NO ACTION
SYS_C001272 EDUCATION R SYS_C001260 NO ACTION
SYS_C001273 EDUCATION R SYS_C001262 CASCADE
SYS_C001274 STAFF C
SYS_C001275 STAFF C
SYS_C001276 STAFF C
SYS_C001277 STAFF P
SYS_C001278 STAFF R SYS_C001265 CASCADE
SYS_C001279 STAFF R SYS_C001263 CASCADE
SYS_C001280 WORK C
SYS_C001281 WORK C
SYS_C001282 WORK C
SYS_C001283 WORK C
SYS_C001284 W8ORK C
SYS_C001285 WORK P
SYS_C001286 WORK R SYS_C001262 CASCADE
SYS_C001287 WORK R SYS_C001277 CASCADE
29 rows selected.
SQL> /* ÷ÙÂÏÒËÁ ÓÐÉÓËÁ ÐÒÅÄÓÔÁ×ÌÅÎÉÊ */
SQL> SELECT SUBSTR(view_name,1,10) AS view_name,
2 text
3 /* SUBSTR(text,1,40) AS text*/
4 FROM all_views
5 WHERE owner='U1';
VIEW_NAME TEXT
---------- ------------------------------------------------------------
EMPLVIEW (
SELECT employee.empl_id, name_last, name_1st, name_2nd,
dep_name,jo
SQL> /* ÷ÙÂÏÒËÁ ÓÐÉÓËÁ ÔÒÉÇÇÅÒÏ× */
SQL> SELECT SUBSTR(trigger_name,1,12) AS trigger_name,
2 SUBSTR(table_name,1,12) AS table_name,
3 trigger_type, triggering_event
4 FROM all_triggers
5 WHERE owner='U1';
TRIGGER_NAME TABLE_NAME TRIGGER_TYPE TRIGGERING_EVENT
------------ ------------ ---------------- --------------------------
T1 WORK BEFORE EACH ROW INSERT
ðÒÏÔÏËÏÌ ×ÙÐÏÌÎÅÎÉÑ ÚÁÐÒÏÓÏ× Ë ËÁÔÁÌÏÇÕ DB2
------------------------------ Command Entered ------------------------------
-- ÷ÙÂÏÒËÁ ÓÐÉÓËÁ ÔÁÂÌÉÃ
SELECT SUBSTR(tabname,1,12) AS tabname
FROM syscat.tables
WHERE definer='DEREV' ;
-----------------------------------------------------------------------------
TABNAME
------------
EMPLVIEW
EDU_LEVEL
EDU_PROFILE
EMPLOYEE
DEPARTMENT
JOB
EDUCATION
STAFF
WORK
9 record(s) selected.
------------------------------ Command Entered ------------------------------
-- ÷ÙÂÏÒËÁ ÓÐÉÓËÁ ÓÔÏÌÂÃÏ× ÔÁÂÌÉÃ
SELECT SUBSTR(tabname,1,12) AS tabname,
SUBSTR(colname,1,12) AS colname,
SUBSTR(typename,1,12) AS typename,
length, nulls
FROM syscat.columns
WHERE tabname IN
(SELECT tabname
FROM syscat.tables
WHERE definer='DEREV')
ORDER BY 1 ;
-----------------------------------------------------------------------------
TABNAME COLNAME TYPENAME LENGTH NULLS
------------ ------------ ------------ ----------- -----
DEPARTMENT DEP_ID INTEGER 4 N
DEPARTMENT DEP_NAME VARCHAR 20 Y
DEPARTMENT SALARY_LIMIT DECIMAL 10 Y
EDU_LEVEL LEVEL_ID INTEGER 4 N
EDU_LEVEL LEVEL_NAME VARCHAR 20 Y
EDU_PROFILE PROF_ID INTEGER 4 N
EDU_PROFILE PROF_NAME VARCHAR 20 Y
EDUCATION LEVEL_ID INTEGER 4 N
EDUCATION PROF_ID INTEGER 4 N
EDUCATION EMPL_ID DECIMAL 10 N
EDUCATION WHEN_YEAR SMALLINT 2 Y
EMPLOYEE EMPL_ID DECIMAL 10 N
EMPLOYEE NAME_LAST VARCHAR 20 N
EMPLOYEE NAME_1ST VARCHAR 20 Y
EMPLOYEE NAME_2ND VARCHAR 20 Y
EMPLOYEE BIRTH DATE 4 Y
EMPLOYEE ADDRESS VARCHAR 80 Y
EMPLOYEE PHONE DECIMAL 7 Y
JOB JOB_ID INTEGER 4 N
JOB JOB_NAME VARCHAR 40 Y
JOB MIN_SALARY DECIMAL 7 Y
JOB MAX_SALARY DECIMAL 7 Y
JOB LEVEL_ID INTEGER 4 N
JOB PROF_ID INTEGER 4 N
JOB EXPERIENCE SMALLINT 2 Y
STAFF STAFF_ID INTEGER 4 N
STAFF JOB_ID INTEGER 4 N
STAFF DEP_ID INTEGER 4 N
STAFF RATE DECIMAL 6 N
WORK EMPL_ID DECIMAL 10 N
WORK STAFF_ID INTEGER 4 N
WORK SALARY DECIMAL 7 Y
WORK START_DATE DATE 4 N
WORK RATE DECIMAL 2 N
WORK MNG_FLAG CHARACTER 1 Y
35 record(s) selected.
------------------------------ Command Entered ------------------------------
-- ÷ÙÂÏÒËÁ ÓÐÉÓËÁ ÔÁÂÌÉÞÎÙÈ ÏÇÒÁÎÉÞÅÎÉÊ ÃÅÌÏÓÔÎÏÓÔÉ
SELECT constname, SUBSTR(tabname,1,12) as tabname, type
FROM syscat.tabconst
WHERE definer='DEREV' ;
-----------------------------------------------------------------------------
CONSTNAME TABNAME TYPE
------------------ ------------ -------------------------
SQL010905150222310 EDU_LEVEL P
SQL010905150225990 EDU_PROFILE P
SQL010905150227190 EMPLOYEE P
SQL010905150228070 DEPARTMENT P
SQL010905150229500 JOB P
SQL010905150229720 JOB F
SQL010905150229940 JOB F
SQL010905150230650 EDUCATION P
SQL010905150230820 EDUCATION F
SQL010905150230821 EDUCATION F
SQL010905150230822 EDUCATION F
SQL010905150231640 STAFF P
SQL010905150231700 STAFF F
SQL010905150231701 STAFF F
SQL010905150233680 WORK P
SQL010905150233681 WORK F
SQL010905150233682 WORK F
SQL010905150233840 WORK K
18 record(s) selected.
------------------------------ Command Entered ------------------------------
-- ÷ÙÂÏÒËÁ ÓÐÉÓËÁ ÏÇÒÁÎÉÞÅÎÉÊ ÃÅÌÏÓÔÎÏÓÔÉ CHECK
SELECT SUBSTR(tabname,1,12) AS tabname,
SUBSTR(text,1,30) AS text
FROM syscat.checks
WHERE definer='DEREV' ;
-----------------------------------------------------------------------------
TABNAME TEXT
------------ ------------------------------
WORK RATE>0 AND RATE<=2
1 record(s) selected.
------------------------------ Command Entered ------------------------------
-- ÷ÙÂÏÒËÁ ÓÐÉÓËÁ ÓÓÙÌÏÞÎÙÈ ÏÇÒÁÎÉÞÅÎÉÊ ÃÅÌÏÓÔÎÏÓÔÉ
SELECT SUBSTR(tabname,1,12) AS tabname,
SUBSTR(reftabname,1,12) AS reftabname,
refkeyname, deleterule, updaterule
FROM syscat.references
WHERE definer='DEREV' ;
-----------------------------------------------------------------------------
TABNAME REFTABNAME REFKEYNAME DELETERULE UPDATERULE
------------ ------------ ------------------ ---------- ----------
JOB EDU_LEVEL SQL010905150222310 C R
JOB EDU_PROFILE SQL010905150225990 C R
EDUCATION EDU_LEVEL SQL010905150222310 R R
EDUCATION EDU_PROFILE SQL010905150225990 R R
EDUCATION EMPLOYEE SQL010905150227190 C R
STAFF JOB SQL010905150229500 C R
STAFF DEPARTMENT SQL010905150228070 C R
8WORK EMPLOYEE SQL010905150227190 C R
WORK STAFF SQL010905150231640 C R
9 record(s) selected.
------------------------------ Command Entered ------------------------------
-- ÷ÙÂÏÒËÁ ÓÐÉÓËÁ ÐÒÅÄÓÔÁ×ÌÅÎÉÊ
SELECT SUBSTR(viewname,1,12) AS viewname,
viewcheck,
SUBSTR(text,1,60) AS text
FROM syscat.views
8 WHERE definer='DEREV' ;
-----------------------------------------------------------------------------
VIEWNAME VIEWCHECK TEXT
------------ --------- ------------------------------------------------------------
EMPLVIEW N CREATE VIEW emplview AS ( SELECT employee.empl_id, name_last
1 record(s) selected.
------------------------------ Command Entered ------------------------------
-- ÷ÙÂÏÒËÁ ÓÐÉÓËÁ ÔÒÉÇÇÅÒÏ×
SELECT SUBSTR(trigname,1,12) AS trigname,
SUBSTR(tabname,1,12) AS tabname,
trigtime, granularity, trigevent
FROM syscat.triggers
WHERE definer='DEREV' ;
-----------------------------------------------------------------------------
TRIGNAME TABNAME TRIGTIME GRANULARITY TRIGEVENT
------------ ------------ -------- ----------- --------------
T1 WORK B R I
1 record(s) selected.