HiveBrain v1.2.0
Get Started
← Back to all entries
snippetModerate

How do you find where a stored procedure is used (in other stored procedures)

Submitted by: @import:stackexchange-dba··
0
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

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.