Thursday, August 4, 2011

Foreign Keys with missing Indexes

The following script will show any Foreign Keys that do not have a corresponding Index:

select fk.owner, fk.table_name, fk.constraint_name, fkc.column_name
from all_constraints fk
, all_cons_columns fkc
where fk.owner = :owner
and fk.constraint_type = 'R'
and not exists ( select null
from all_indexes ind
where ind.index_name = fk.constraint_name
and ind.owner = fk.owner )
and fkc.owner = fk.owner
and fkc.table_name = fk.table_name
and fkc.constraint_name = fk.constraint_name
and not exists ( select null
from all_constraints pk
, all_cons_columns pkc
where pk.constraint_type = 'P'
and pk.owner = fk.owner
and pk.table_name = fk.table_name
and pkc.owner = pk.owner
and pkc.table_name = pk.table_name
and pkc.constraint_name = pk.constraint_name
and fkc.column_name = pkc.column_name )
order by owner, table_name, constraint_name;