Home > Scripts > How to find dependency objects for any object?

How to find dependency objects for any object?

July 2, 2013

There are situations where we may need to find the dependencies for any object like procedure etc….

We can view directly in SQL DEVELOPER EASILY in user friendly manner.  However there could be situations where we do not have access to SQLDEVELOPER or any other graphical tool.  During this circumstances, we can make use of below script.

 

The dba_dependencies view describes all dependencies in the database between procedures, packages, functions, package bodies, and triggers, including dependencies on views created without any database links.

select
referenced_owner,
referenced_name,
referenced_type
from
dba_dependencies
where
name= ‘<My procedure name’
and
owner = ‘<ONWER>’  —>  Optional.
order by
referenced_owner, referenced_name, referenced_type;

 

In addition above, i got  many variants of the above scripts from google.com  and thought of putting the same in single place to make use of the same.

 

VARIANT 1:-

set lines 132

select distinct
owner,
name,
referenced_name
from
dba_dependencies
where
type in (‘PACKAGE BODY’,’PROCEDURE’,’TRIGGER’)
and
referenced_type = ‘SEQUENCE’;

VARIANT 2:-

select
referenced_name,
referenced_type
from
dba_dependencies
where
name=’my_tablename’;

VARIANT 3:-

select
referenced_name,
referenced_type
from
dba_dependencies
where
name=’CUSTOMER’
and
referenced_type=’TABLE’;

VARIANT 4:-

column c1 heading “object|level”         format a16
column c2 heading “object|name”          format a40
column c3 heading “referencing|object”   format a40

select
lpad (‘ ‘, 2 * (level – 1)) || to_char (level, ‘999’) as c1,
owner || ‘.’ || name || ‘ (‘ || type || ‘)’ as c2,
referenced_owner || ‘.’ || referenced_name || ‘ (‘ || referenced_type || ‘)’ as c3
from
dba_dependencies
start with
owner = ‘SYS’
and
name = ‘my_tablename’
connect by prior
referenced_owner = owner
and prior
referenced_name = name
and prior
referenced_type = type
and
type = ‘TABLE’;

 

FOLLOWING ARE THE COLUMNS AVAILABLE.

(owner,
NAME,
TYPE,
referenced_owner,
referenced_name,
referenced_type,
referenced_link_name,
dependency_type
)

Today i need to find which views built on another view. following is the query which i used as below.

select * from dba_dependencies where name like upper(”) and referenceD_type=’VIEW’;

Advertisements
Categories: Scripts
%d bloggers like this: