|
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,30) TABLE_NAME,
SUBSTR(CONSTRAINT_NAME,1,30) CONSTRAINT_NAME,
SUBSTR(COLUMN_NAME,1,30) 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
) |
Suggested Jobs More Jobs >>
|
