Wednesday, February 25, 2009

Query to find un-indexed Foreigh Key Constraints

SELECT * FROM (SELECT c.table_name, cc.column_name, cc.position column_position FROM user_constraints c, user_cons_columns cc
WHERE c.constraint_name = cc.constraint_name AND c.constraint_type = 'R' MINUS SELECT i.table_name, ic.column_name, ic.column_position
FROM user_indexes i, user_ind_columns ic
WHERE i.index_name = ic.index_name )
ORDER BY table_name, column_position ;