How can I search the full text of a stored procedure for a value? 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?
Use one of


sys.sql_modules
Red Gate SQL Search (free!)
OBJECT_DEFINITION


Never use


INFORMATION_SCHEMA
sys.syscomments