16 | 12 | 2018
Latest Articles
Popular Articles

Scripts

Discover Table Dependancy ( parent child )

User Rating:  / 0
PoorBest 

Discover table dependency with SQL

 

This is a script I wrote some time ago. It is usefull to list which tables depend on another table.  I think to remember this scripts returns an error when a table has a self referencing constraint.  ( "foreign" key to itself )

 

define OWNER='COCONUT';

define PARENT_TABLE ='T_PARENT' ;

select level,lpad('> ',5*(level-1)) || to_char(child) "TABLE" from
(select b.table_name "PARENT",a.table_name "CHILD"
from all_constraints a,all_constraints b
where a.owner=b.owner
and a.owner='&OWNER'
and a.constraint_type='R'
and a.r_constraint_name=b.constraint_name
order by b.table_name,a.table_name)
start with parent='&PARENT_TABLE'
connect by prior child = parent
 

 

Sample output ( table names off course changed )

 

LEVEL    TABLE

1    T_11
2       > T_21
2       > T_22
3            > T_31
3            > T_32
3            > T_33
3            > T_34
4                 > T_41
5                      > T_51
4                 > T_42
3            > T_35
3            > T_36
3            > T_36
3            > T_37
3            > T_38
2       > T_23
3            > T_39
3            > T_310
3            > T_311
3            > T_312
2       > T_24
3            > T_313
3            > T_314
3            > T_315
4                 > T_43
3            > T_316
3            > T_317
4                 > T_44
3            > T_318
3            > T_319
2       > T_25
2       > T_26
3            > T_320
3            > T_321
3            > T_322
3            > T_323
3            > T_325
4                 > T_45
3            > T_326
4                 > T_46
5                      > T_52
4                 > T_47
3            > T_327
3            > T_328
3            > T_329
3            > T_330
3            > T_331
3            > T_332
3            > T_333
3            > T_334
2       > T_27