A very convenient way to search for specific text in stored procedures is to use the view INFORMATION_SCHEMA.ROUTINES.
If you type this SQL query :
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
you’ll see a lot of information about the stores procedures of the database where you executed your query, including the code of the procedure.
Now if you have a lot of stored procedures, are looking for a specific word and need some answers quick, try this:
SELECT ROUTINE_NAME, LAST_ALTERED FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_DEFINITION LIKE '%yourTextHere%' ORDER BY ROUTINE_NAME
You just need to Execute that and you got your answer!
