Document Type | Technical Information
Category | Monitoring/Inspection
Document Number | TMOTI029
Overview
This guide provides a query to find tables that have Foreign Key constraints applied.
You can query all Foreign Keys (FK, foreign keys) for the entered table as well as Foreign Keys and Self RI constraints for the referenced tables.
Method
Usage Example
et linesize 110
set verify off
set pagesize 40
column owner format a5
column r_owner format a5
column column_name format a12
column tt noprint
column position heading P format 9
column table_name format a15
column r_table_name format a15
column constraint_name format a15
column r_constraint_name format a15
select
a.tt,
a.owner,
b.table_name,
a.constraint_name,
b.column_name,
b.position,
a.r_constraint_name,
c.column_name,
c.position,
c.table_name r_table_name,
a.r_owner
from
(select
owner,
constraint_name,
r_constraint_name,
r_owner,1 tt
from
dba_constraints
where
owner=upper('&&owner')
and table_name=upper('&&table_name')
and constraint_type!='C'
union
select
owner,
constraint_name,
r_constraint_name,
r_owner,2
from
dba_constraints
where
(r_constraint_name,r_owner) in
(select
constraint_name,
owner
from
dba_constraints
where
owner=upper('&owner')
and table_name=upper('&table_name'))
) a,
dba_cons_columns b,
dba_cons_columns c
where
b.constraint_name=a.constraint_name
and b.owner=a.owner
and c.constraint_name=a.r_constraint_name
and c.owner=a.r_owner
and b.position=c.position
order by 1,2,3,4,5
/
set verify on
clear columns
undef owner
undef table_name