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,
user_indexes ui 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 and ui.table_name = i.table_name and ui.index_name = i.index_name and ui.index_type IN (‘NORMAL’,
‘NORMAL/REV’)
GROUP BY i.index_name);
Recent Posts
