How to search for a table name in all your stored procedures

by Andy
Posted on September 18, 2014July 29, 2020

[AdSense-A]
This quick and dirty query can search for a table name in all your stored procedures within a database. There is really nothing special about the query and I am sure most database administrators will know how to do this. It is really just for my own reference and anyone who else may need something like it.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%tblIAmLookingFor%'
AND ROUTINE_TYPE='PROCEDURE'

SELECT ROUTINE_NAME, ROUTINE_DEFINITION

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_DEFINITION LIKE '%tblIAmLookingFor%'

AND ROUTINE_TYPE='PROCEDURE'

The query itself isn’t all that special, you are, instead of using the query to search data you are searching the stored procedures themselves. Which is a neat little trick, that any programmer should know how to do. I am a firm believer that the more tricks like this that you know, or at least know where to find them, the better a programmer you will be.