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!