5

Through Oracle queries is it possible to find out which views/synonyms/tables a stored procedure use?

In PL/SQL Developer if you collapse out a stored procedure it will show "References" which shows all the tables/views/synonyms that the stored procedure uses.

I am trying to implement this functionality into a script which will come in handy.

Wondering if anyone knows a script that will fetch me all the synonyms/views/tables that a stored procedure uses?

APC
  • 144,005
  • 19
  • 170
  • 281
jason
  • 345
  • 2
  • 4
  • 9

2 Answers2

7

The information you are looking for is in the user_dependencies/all_dependencies view.

Rene
  • 10,391
  • 5
  • 33
  • 46
3

The answer by @Rene is correct however I believe it needs additional explanation. When selecting from all_dependencies you might run query like one below that should give you all the objects that are referencing your SP.

SELECT *
FROM all_dependencies
WHERE "REFERENCED_NAME" = 'vcustomeraddresses';

You might be surprised when it will come back empty-handed.
This is because oracle is CASE SENSITIVE. What this means is that you have to ether disable case sensitivity (if version of oracle you are using is above 10g r2)

ALTER SESSION SET NLS_COMP=LINGUISTIC;
ALTER SESSION SET NLS_SORT=BINARY_CI;

or upper both sides when comparing 

SELECT *
FROM all_dependencies
WHERE upper("REFERENCED_NAME") = upper('vcustomeraddresses');

Hope this saves you some time and frustration.

Community
  • 1
  • 1
Matas Vaitkevicius
  • 58,075
  • 31
  • 238
  • 265