Menu
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?
315
To get names of all synonyms from Oracle database or from an specific table you can use: USER_SYNONYMS, ALL_SYNONYMS, DBA_SYNONYMS, USER_OBJECTS. Privileges may be required to query certain tables or views. The most commonly used to list synonyms in a database are USER_SYNONYMS and USER_OBJECTS.
Connect to your SQL instance, expand the database, and navigate to the Synonyms folder. Right-click on it and choose New Synonym. Enter the required details for the Synonym name, Synonym schema, Database Name, Object schema, Object Type, and name.
The information you are looking for is in the user_dependencies/all_dependencies view.
170
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
