Scripts
Discover Table Dependancy ( parent child )
/ 0
- Details
- Parent Category: Articles
- Created on Wednesday, 17 March 2010 22:34
- Last Updated on Monday, 12 March 2012 13:54
- Published on Wednesday, 17 March 2010 22:34
- Hits: 2885
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 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 )
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