I use the following script to search the text of all stored procedures when I want to find specific values.
SELECT ROUTINE_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE @searchText ORDER BY ROUTINE_NAME
I recently discovered that
ROUTINE_DEFINITION stops after 4000 characters, so some procedures were not getting returned when they should have been.
How can I query the full text of a stored procedure for a value?