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

StackExchange Database Administrators Q#41765, answer score: 7