Find tables referenced inside PL/SQL stored procedures Oracle Database Tips by Donald Burleson

 

Question:   I have hundreds of Stored procedures, and I need to know all the tables and column names used in each PL/SQL stored procedure. In a stored procedure, there are can many select, Update, insert, create, truncate statements.  I want to know all the columns used in the query, all the tables from which the data is fetched.

How do I query the dictionary to see the tables within by stored procedures?

 

Answer:  First, see these notes on using the dba_dependencies view.

The Oracle data dictionary tracks the object types referenced in PL/SQL with the dba_dependencies view.  To track the dependency among packages and tables, try this dictionary query:

select
   referenced_owner,
   referenced_name,
   referenced_type
from
   dba_dependencies
where
   name= 'MYPROC'
and
   owner = 'SCOTT'
order by
   referenced_owner, referenced_name, referenced_type;

 

Here is a more sophisticated script to show all tables inside a stored procedure, published by Antony Ananth:

select
   owner,
   type,
   name,
   referenced_owner,
   referenced_type,
   referenced_name
from
   dba_dependencies
where
   ((owner like upper('&1')
   and
      name like upper('&2') )
   or
   (referenced_owner like upper('&1')
   and
   referenced_name like upper('&2') ))
   and
   referenced_owner != 'SYS'
   and
   referenced_type != 'NON-EXISTENT'
order by
   owner, type, name;

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.