How to Find a Stored Procedure Containing Text
I have often needed to find a stored procedure that contains a certain snippet of text, such as a text in a subject line. Below is a query I've used in the past but it has been pointed out to me that this has a fatal flaw: It only searches the first 4000 characters of the stored procedure:.
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%SEARCHSTRING%' AND ROUTINE_TYPE='PROCEDURE'
An improved query is to use the following:
SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id) FROM sys.procedures WHERE OBJECT_DEFINITION(object_id) LIKE '%SearchString%'
Thanks to firstname.lastname@example.org for the heads up.