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.
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.
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:
But you could also apply the function to the results of another query. Try this on for size.
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!