All geek questions in one place

How to find out which tables / views / synonyms are used in a stored procedure

With Oracle queries, can I find out what kinds / synonyms / tables use a stored procedure?

In PL / SQL Developer, if you destroy the stored procedure, it will display “Links”, which will show all the tables / views / synonyms used in the stored procedure.

I am trying to implement this functionality in a script that comes in handy.

I wonder if anyone knows the script that will get all the synonyms / views / tables that the stored procedure uses?

+4
source share
2 answers

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

+7
source

The answer to @Rene is correct, but I believe that he needs an additional explanation. When choosing from all_dependencies you can run a query similar to the one below, which should provide you with all the objects that reference your SP.

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

You may be surprised when he returns empty-handed.
This is because the oracle is SENSITIVE. This means that you have to disable case sensitivity (if you are using the oracle version 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 time and frustration.

+1
source

Source: https://habr.com/ru/post/1313473/

More articles:


All Articles