SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%FOO%' AND ROUTINE_TYPE='PROCEDURE'Method 2:
SELECT OBJECT_NAME(ID) FROM SYSCOMMENTS WHERE [TEXT] LIKE '%FOO%' AND OBJECTPROPERTY(ID, 'ISPROCEDURE') = 1 GROUP BY OBJECT_NAME(ID)Method 3:
SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1 AND definition LIKE '%FOO%'
Remember routine_definition is cropped at 4000 chars if you have a long stored procedure. sys.sql_modules doesn't have such restriction.
So that's it. Hopefully you will find this tutorial very handy.