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 biradar26@gmail.com for the heads up.