Display list of NonIndexed foreign keys

SET SERVEROUTPUT ON
SET PAGESIZE 1000
SET LINESIZE 255
SET FEEDBACK OFF

SELECT t.table_name,
c.constraint_name,
c.table_name table2,
acc.column_name
FROM all_constraints t,
all_constraints c,
all_cons_columns acc
WHERE c.r_constraint_name = t.constraint_name
AND c.table_name = acc.table_name
AND c.constraint_name = acc.constraint_name
AND NOT EXISTS (SELECT ‘1’
FROM all_ind_columns aid
WHERE aid.table_name = acc.table_name
AND aid.column_name = acc.column_name)
ORDER BY c.table_name;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: