Copy Code code as follows:
COLUMN COLUMNS format A30 word_wrapped
COLUMN tablename Format A15 word_wrapped
COLUMN constraint_name Format A15 word_wrapped
SELECT table_name,
Constraint_name,
CNAME1 | | NVL2 (CNAME2, ', ' | | CNAME2, NULL) | |
NVL2 (CNAME3, ', ' | | CNAME3, NULL) | |
NVL2 (CNAME4, ', ' | | CNAME4, NULL) | |
NVL2 (CNAME5, ', ' | | CNAME5, NULL) | |
NVL2 (CNAME6, ', ' | | CNAME6, NULL) | |
NVL2 (CNAME7, ', ' | | CNAME7, NULL) | |
NVL2 (CNAME8, ', ' | | CNAME8, NULL) COLUMNS
From (SELECT B.table_name,
B.constraint_name,
MAX (DECODE (POSITION, 1, column_name, NULL)) CNAME1,
MAX (DECODE (POSITION, 2, COLUMN_NAME, NULL)) CNAME2,
MAX (DECODE (POSITION, 3, COLUMN_NAME, NULL)) CNAME3,
MAX (DECODE (POSITION, 4, COLUMN_NAME, NULL)) CNAME4,
MAX (DECODE (POSITION, 5, COLUMN_NAME, NULL)) CNAME5,
MAX (DECODE (POSITION, 6, COLUMN_NAME, NULL)) CNAME6,
MAX (DECODE (POSITION, 7, COLUMN_NAME, NULL)) CNAME7,
MAX (DECODE (POSITION, 8, COLUMN_NAME, NULL)) CNAME8,
COUNT (*) col_cnt
From (SELECT SUBSTR (TABLE_NAME, 1) TABLE_NAME,
SUBSTR (constraint_name, 1) constraint_name,
SUBSTR (column_name, 1) column_name,
POSITION
From User_cons_columns) A,
User_constraints B
WHERE A.constraint_name = B.constraint_name
and B.constraint_type = ' R '
GROUP by B.table_name, B.constraint_name) CONS
WHERE col_cnt > All
(SELECT COUNT (*)
From User_ind_columns I
WHERE i.table_name = CONS. table_name
and I.column_name in (CNAME1, CNAME2, CNAME3, CNAME4, CNAME5,
CNAME6, CNAME7, CNAME8)
and I.column_position <= CONS. Col_cnt
GROUP by I.index_name)
/
On the basis of the above modified a bit, you can check all the users.
Copy Code code as follows:
SET Linesize 400;
COLUMN OWNER format A10 word_wrapped
COLUMN COLUMNS format A30 word_wrapped
COLUMN table_name Format A15 word_wrapped
COLUMN constraint_name format A40 word_wrapped
SELECT OWNER,
TABLE_NAME,
Constraint_name,
CNAME1 | | NVL2 (CNAME2, ', ' | | CNAME2, NULL) | |
NVL2 (CNAME3, ', ' | | CNAME3, NULL) | |
NVL2 (CNAME4, ', ' | | CNAME4, NULL) | |
NVL2 (CNAME5, ', ' | | CNAME5, NULL) | |
NVL2 (CNAME6, ', ' | | CNAME6, NULL) | |
NVL2 (CNAME7, ', ' | | CNAME7, NULL) | |
NVL2 (CNAME8, ', ' | | CNAME8, NULL) COLUMNS
From (SELECT B.owner,b.table_name,
B.constraint_name,
MAX (DECODE (POSITION, 1, column_name, NULL)) CNAME1,
MAX (DECODE (POSITION, 2, COLUMN_NAME, NULL)) CNAME2,
MAX (DECODE (POSITION, 3, COLUMN_NAME, NULL)) CNAME3,
MAX (DECODE (POSITION, 4, COLUMN_NAME, NULL)) CNAME4,
MAX (DECODE (POSITION, 5, COLUMN_NAME, NULL)) CNAME5,
MAX (DECODE (POSITION, 6, COLUMN_NAME, NULL)) CNAME6,
MAX (DECODE (POSITION, 7, COLUMN_NAME, NULL)) CNAME7,
MAX (DECODE (POSITION, 8, COLUMN_NAME, NULL)) CNAME8,
COUNT (*) col_cnt
From (SELECT SUBSTR (TABLE_NAME, 1) TABLE_NAME,
SUBSTR (constraint_name, 1) constraint_name,
SUBSTR (column_name, 1) column_name,
POSITION
From Dba_cons_columns WHERE OWNER is in (' SYS ', ' SYSTEM ', ' Sysman ', ' HR ', ' OE ', ' Exfsys ', ' dbsnmp ', ' Mdsys ', ' Olapsys ', ' SCOTT ', ' Exfsys ', ' SH ', ' PM ', ' Ctxsys ') A,
Dba_constraints B
WHERE A.constraint_name = B.constraint_name
and B.constraint_type = ' R '
GROUP by B.owner,b.table_name, B.constraint_name) CONS
WHERE col_cnt > All
(SELECT COUNT (*)
From Dba_ind_columns I
WHERE i.table_name = CONS. TABLE_NAME and i.table_owner=cons. OWNER
and I.column_name in (CNAME1, CNAME2, CNAME3, CNAME4, CNAME5,
CNAME6, CNAME7, CNAME8)
and I.column_position <= CONS. Col_cnt
GROUP by I.index_name)
/
