snippetModerate
How do you find where a stored procedure is used (in other stored procedures)
Viewed 0 times
storedprocedureshowyouusedwhereprocedurefindother
Problem
I have a stored procedure which I want to refactor, in a database which has thousands of SPs. Is there a quick way of finding references to that stored procedure in other SPs so I can be sure that I'm not breaking any other code when I refactor.
In the application code I can look for calls to the SP easily enough, and I can do a text search over all the various sql files which define the SPs, but it's possible there may be some SPs in the database which may be missed that way.
EDIT: The stored procedures I'm trying to locate are part of a package.
EDIT: I'm running on Oracle 11g
In the application code I can look for calls to the SP easily enough, and I can do a text search over all the various sql files which define the SPs, but it's possible there may be some SPs in the database which may be missed that way.
EDIT: The stored procedures I'm trying to locate are part of a package.
EDIT: I'm running on Oracle 11g
Solution
DBA_DEPENDENCIES view has all the answers to such questions.select * from DBA_DEPENDENCIES
where referenced_owner='HR' and referenced_name='STORED_PROCEDURE_41';Code Snippets
select * from DBA_DEPENDENCIES
where referenced_owner='HR' and referenced_name='STORED_PROCEDURE_41';Context
StackExchange Database Administrators Q#88731, answer score: 13
Revisions (0)
No revisions yet.