找出当前用户下所有表的未索引的外码

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

=========================================================

SQL> column columns format a30 word_wrapped
column tablename format a15 word_wrapped
SQL> column constraint_name format a15 word_wrapped
SQL> select table_name, constraint_name,
SQL> cname1 || nvl2(cname2,','||cname2,null) ||
  2  nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
  3  nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
  4  nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
  5  columns
  6  from ( select b.table_name,
  7  b.constraint_name,
  8  max(decode( position, 1, column_name, null )) cname1,
  9   10   max(decode( position, 2, column_name, null )) cname2,
 11   max(decode( position, 3, column_name, null )) cname3,
 12   max(decode( position, 4, column_name, null )) cname4,
 13   max(decode( position, 5, column_name, null )) cname5,
 14   max(decode( position, 6, column_name, null )) cname6,
 15   max(decode( position, 7, column_name, null )) cname7,
 16   max(decode( position, 8, column_name, null )) cname8,
 17   count(*) col_cnt
 18   from (select substr(table_name,1,30) table_name,
 19   substr(constraint_name,1,30) constraint_name,
 20   substr(column_name,1,30) column_name,   position
 21   from user_cons_columns ) a,
 22   user_constraints b
 23   where a.constraint_name = b.constraint_name
 24   and b.constraint_type = 'R'
 25   group by b.table_name, b.constraint_name
 26   ) cons
 27   where col_cnt > ALL
 28   ( select count(*)
 29   from user_ind_columns i
 30   where i.table_name = cons.table_name
 31   and i.column_name in (cname1, cname2, cname3, cname4,
 32   cname5, cname6, cname7, cname8 )
 33   and i.column_position <= cons.col_cnt
 34   group by i.index_name
 35   )
 36   /

TABLE_NAME         CONSTRAINT_NAME COLUMNS
------------------------------ --------------- ------------------------------
C          SYS_C0011160    Y
DEPARTMENTS         DEPT_MGR_FK     MANAGER_ID
COUNTRIES         COUNTR_REG_FK   REGION_ID

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7551038/viewspace-616686/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7551038/viewspace-616686/