개요
Foreign Key 제약조건에 걸린 테이블 찾는 쿼리를 안내합니다.
입력된 테이블에 대한 모든 Foreign Key(FK, 외래키) 와 참조되는 테이블에 대한 Foreign Key 및 Self RI constraints를 조회할 수 있습니다.
방법
사용 예제
set 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
SQL
복사