Tables References & Referenced By

We create many tables in a schema with lot of Constraints among them. The linking constraint Foreign Key which exists among tables will sometime create spaghetti schema. Consider a schema with 4 tables as show in the ER diagram below. (You can download the script for creating these tables and constrains)

It may be some times required to fetch the foreign key relationship between tables to identify the Parent-Child relationship. To identify those you can use the following queries will be handy for you. This query uses the sys user’s constraint definition tables for the database.

To Identify the Tables referencing the Given Table:

select ro.name table_name,
substr(initcap(cc.column_name), 1, 30) column_name, cc.position,
cn.name constraint_name, ru.name owner
from sys.obj$ o, sys.user$ u, sys.con$ cn, sys.cdef$ c, sys.obj$ ro,
dba_cons_columns cc, sys.con$ rcn, sys.user$ ru
where o.owner# = u.user#
and u.name = '&&SCHEMANAME'
and o.name = '&&TABLENAME'
and c.robj# = o.obj#
and c.con# = cn.con#
and ro.obj# = c.obj#
and cc.owner = u.name
and cc.table_name = o.name
and rcn.con# = c.rcon#
and cn.owner# = ru.user#
and c.type# = 4
and cc.constraint_name = rcn.name
order by u.name, ro.name, cn.name, cc.position;

To Identify the Tables referred by the Given Table:

select a.table_name, initcap(b.column_name) column_name, b.position,
a.constraint_name, a.owner
from (select ao.name table_name,
au.name owner,
bcn.name constraint_name,
decode(ac.type#, 4,
decode(ac.refact, 1, 'CASCADE', 2, 'SET NULL', 'NO ACTION'),
null) delete_rule,
bo.name tn
from sys.cdef$ bc, sys.con$ bcn, sys.obj$ bo, sys.user$ bu,
sys.con$ brc, sys.user$ bru, sys.obj$ bro,
sys.cdef$ ac, sys.con$ acn, sys.obj$ ao, sys.user$ au
where bc.con# = bcn.con#
and bc.obj# = bo.obj#
and bo.owner# = bu.user#
and bu.name = '&&SCHEMANAME'
and bo.name = '&&TABLENAME'
and bc.rcon# = brc.con#(+)
and brc.owner# = bru.user#(+)
and bc.robj# = bro.obj#(+)
and ac.con# = bc.rcon#
and ac.con# = acn.con#
and ao.obj# = ac.obj#
and ao.owner# = au.user#
and ac.type# in (2,3)) a,
dba_cons_columns b
where b.table_name = a.tn
and b.constraint_name = a.constraint_name
order by a.table_name, a.constraint_name, b.position;

Hope this is useful for you at times :)