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?
INFORMATION_SCHEMA
is an obsolete and incomplete list of sql objects left over from the SQL 2000 days, and you'd be better off using the newer replacements such as catalog views. From the way you and gbn referenced it, I was expecting something truly horrible to happen if I kept using it :) – Rachel May 8 '13 at 15:22