Find Stored Procedures Referencing…

Time and time again, I find myself trying to find all the stored procedures that reference a specific database object.  It usually comes around when I’m working on changes to a table’s structure, or I’m replacing a table with a view.  I’ll usually reach for the following query (or something like it.

 1: SELECT ROUTINE_NAME

 

 2: FROM INFORMATION_SCHEMA.ROUTINES

 

 3: WHERE

 

 4:     ROUTINE_DEFINITION LIKE '%searchTerm%' 

 

 5:     AND ROUTINE_TYPE='PROCEDURE'

 

Today I decided it was time I created a function to accomplish this same task.  That way I could always use the same tool to locate stored procedures that reference X.

So I created this.

 1: CREATE FUNCTION f_findProceduresReferencing (@searchTerm VARCHAR(500) )

 

 2: RETURNS @results TABLE (

 

 3:      routine_Name SYSNAME

 

 4: )

 

 5: AS

 

 6: BEGIN

 

 7:      INSERT INTO @results

 

 8:      SELECT ROUTINE_NAME

 

 9:      FROM INFORMATION_SCHEMA.ROUTINES

 

 10:      WHERE

 

 11:           ROUTINE_DEFINITION LIKE '%' + @searchTerm + '%'

 

 12:           AND ROUTINE_TYPE='PROCEDURE'

 

 13:

 

 14: RETURN

 

 15: END

 

I want you to see that for the input, I’m taking a VARCHAR.  That way you can give partial names.  but for the output, I’m returning SYSNAME, that way the results can be used as inputs to other queries.

Now that we have a function, you can use it in a simple query:

 1: SELECT * FROM dbo.f_findProceduresReferencing('tableName')

 

But you could also apply the function to the results of another query.  Try this on for size.

 1: SELECT TOP 10 so.name, f.ROUTINE_NAME

 

 2: FROM sys.objects so

 

 3: CROSS APPLY  dbo.f_findProceduresReferencing(so.name) f

 

 4: WHERE

 

 5:     type = 'u'

 

 6: ORDER BY

 

 7:     so.Name

 

This is the reason I created my function.  I can then select the tables I’m replacing, and this function will show me all the stored procedures I have to update before removing the original tables.  That’s the payoff!

1 Comment on "Find Stored Procedures Referencing…"


Leave a Reply

Your email address will not be published. Required fields are marked *