-- To get all parent tables recursively
select
parent_owner,
parent_tab,
child_owner,
child_tab,
level
from
( /* Self join of all_constraints to get
all parent and child tables in database */
select
parent_con.owner parent_owner,
parent_con.table_name parent_tab,
child_con.owner child_owner,
child_con.table_name child_tab
from
all_constraints parent_con,
all_constraints child_con
where
child_con.r_constraint_name = parent_con.constraint_name
)
connect by nocycle prior parent_tab = child_tab
start with
child_owner = 'MURTY'
and child_tab = 'TAB1'
order by level;
-- To get all child tables recursively --
select
parent_owner,
parent_tab,
child_owner,
child_tab,
level
from
( /* Self join of all_constraints to get
all parent and child tables in database */
select
parent_con.owner parent_owner,
parent_con.table_name parent_tab,
child_con.owner child_owner,
child_con.table_name child_tab
from
all_constraints parent_con,
all_constraints child_con
where
child_con.r_constraint_name = parent_con.constraint_name
)
connect by nocycle parent_tab = prior child_tab
start with
parent_owner = 'MURTY'
and parent_tab = 'TAB1'
order by level;
-- This is regarding second query "To get all child tables recursively"
ReplyDeleteThank you Murty for providing the query to get all the child[ childs,grant childs... etc] tables. As per my requriemnet I need to get the foriegn keys of the child tables.
--- To get the foriegn keys of the child tables
select CONSTRAINT_NAME, DELETE_RULE from dba_constraints where CONSTRAINT_NAME in
(select
child_fk
from
( /* Self join of all_constraints to get
all parent and child tables in database */
select
parent_con.owner parent_owner,
parent_con.table_name parent_tab,
child_con.owner child_owner,
child_con.table_name child_tab,
child_con.constraint_name child_fk
from
user_constraints parent_con,
user_constraints child_con
where
child_con.r_constraint_name = parent_con.constraint_name
)
connect by nocycle parent_tab = prior child_tab
start with
parent_owner = 'MURTY'
and parent_tab = 'TAB1'
)
and DELETE_RULE!='CASCADE';