Document Type | Technical Information
Category | Monitoring/Inspection
Applicable Product Versions | Tibero6, Tibero7
Document Number | TMOTI061
Overview
Queries Foreign Key Constraints related to the entered table.
Queries all Foreign Keys defined on the entered table.
Queries Foreign Keys of tables referenced by the entered table.
Queries Self Foreign Key Constraints.
Method
Query
set linesize 110
set pagesize 40
set verify off
column owner format a5
column r_owner format a5
column column_name format a12
column table_name format a15
column r_table_name format a15
column constraint_name format a15
column r_constraint_name format a15
column position heading P format 9
column tt off
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