Sometimes we might need to find procedures that contain certain strings in order to know which procedures affect specific processes in our system and in case of changes to those processes which procedures would we need to modify, therefore we need a simple way that will let us easily find all procedures that contain certain strings.
The following queries (that return the same results) will help you find all procedures that contain a certain string (“Foo” in this example):
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
WHERE ROUTINE_DEFINITION LIKE '%Foo%'
WHERE [text] LIKE '%Foo%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id)
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
AND definition LIKE '%Foo%'
So, as we can see, it is simple to find the names of all procedures that contain a certain string in their code and save expensive development time.