SQL Script Unindexed Foreign Key Listing

Updated: 11-Feb-2004
Version: 1.0

Description

This script lists tables with foreign keys which are not indexed. Unindexed foreign keys are a common cuase of deadlocks

Parameters

own - Table Owner

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

column columns format a20 word_wrapped
column table_name format a30 word_wrapped
col status form a5

set pages 2000 verify off

ACCEPT own PROMPT 'Enter value for owner (RETURN for ALL) > '

spool unindex.lst

select decode( b.table_name, NULL, '****', 'ok' ) Status, 
	   a.table_name, a.columns, b.columns
from 
( select a.table_name, a.constraint_name, 
	     max(decode(position, 1,      column_name,NULL)) || 
	     max(decode(position, 2,', '||column_name,NULL)) || 
	     max(decode(position, 3,', '||column_name,NULL)) || 
	     max(decode(position, 4,', '||column_name,NULL)) || 
	     max(decode(position, 5,', '||column_name,NULL)) || 
	     max(decode(position, 6,', '||column_name,NULL)) || 
	     max(decode(position, 7,', '||column_name,NULL)) || 
	     max(decode(position, 8,', '||column_name,NULL)) || 
	     max(decode(position, 9,', '||column_name,NULL)) || 
	     max(decode(position,10,', '||column_name,NULL)) || 
	     max(decode(position,11,', '||column_name,NULL)) || 
	     max(decode(position,12,', '||column_name,NULL)) || 
	     max(decode(position,13,', '||column_name,NULL)) || 
	     max(decode(position,14,', '||column_name,NULL)) || 
	     max(decode(position,15,', '||column_name,NULL)) || 
	     max(decode(position,16,', '||column_name,NULL)) columns
    from all_cons_columns a, all_constraints b
   where a.constraint_name = b.constraint_name
     and b.constraint_type = 'R'
     and b.owner like UPPER('%&own%')
   group by a.table_name, a.constraint_name ) a, 
( select table_name, index_name, 
	     max(decode(column_position, 1,      column_name,NULL)) || 
	     max(decode(column_position, 2,', '||column_name,NULL)) || 
	     max(decode(column_position, 3,', '||column_name,NULL)) || 
	     max(decode(column_position, 4,', '||column_name,NULL)) || 
	     max(decode(column_position, 5,', '||column_name,NULL)) || 
	     max(decode(column_position, 6,', '||column_name,NULL)) || 
	     max(decode(column_position, 7,', '||column_name,NULL)) || 
	     max(decode(column_position, 8,', '||column_name,NULL)) || 
	     max(decode(column_position, 9,', '||column_name,NULL)) || 
	     max(decode(column_position,10,', '||column_name,NULL)) || 
	     max(decode(column_position,11,', '||column_name,NULL)) || 
	     max(decode(column_position,12,', '||column_name,NULL)) || 
	     max(decode(column_position,13,', '||column_name,NULL)) || 
	     max(decode(column_position,14,', '||column_name,NULL)) || 
	     max(decode(column_position,15,', '||column_name,NULL)) || 
	     max(decode(column_position,16,', '||column_name,NULL)) columns
    from all_ind_columns 
    where index_owner like UPPER('%&own%')   
group by table_name, index_name ) b
where a.table_name = b.table_name (+)
  and b.columns (+) like a.columns || '%'
/

spool off

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links