RE: Dependent objects

>-----Original Message-----
>From: Yttri, Lisa [mailto:lisa.yttri_at_cnh.com]
>
>Does anyone have (or know where I can find) a script to identify
> dependencies on a table?  When we make table modifications,
> we would like to be able to run a script that recursively
> identifies dependent objects.  We pulled one from Metalink,
> but it doesn't seem to work.

This is probably not the best or most efficient way, but it's a "quick and dirty" approach. It should give you the correct results as long as you don't have objects that depend on themselves.

create table my_dependencies as select * from dba_dependencies ; set linesize 120
column parent format a58
column child format a58
define owner = "XXX"
define object = "YYY"
 select
   referenced_type || ' "' || referenced_owner || '"."' ||
      referenced_name || '"' as parent,
   type || ' "' || owner || '"."' || name || '"' as child
  from
    my_dependencies
  start with
    referenced_owner = '&owner'
    and referenced_name = '&object'
  connect by
    referenced_owner = prior owner
    and referenced_name = prior name
    and referenced_type = prior type

union
 select
   referenced_type || ' "' || referenced_owner || '"."' ||
      referenced_name || '"' as parent,
   type || ' "' || owner || '"."' || name || '"' as child
  from
    my_dependencies
  start with
    owner = '&owner'
    and name = '&object'
  connect by
    owner = prior referenced_owner
    and name = prior referenced_name
    and type = prior referenced_type

order by
  1, 2 ;

drop table my_dependencies ; Received on Thu Apr 05 2001 - 17:02:11 CDT