Disclaimer: I am a consultant at Amazon Web Services, and this is my personal blog. The opinions expressed here are solely mine and do not reflect the views of Amazon Web Services (AWS). Any statements made should not be considered official endorsements or statements by AWS.
Below small query will list all the procedures that contains specific text.
SELECT OBJECT_NAME(object_id), definition FROM sys.sql_modules WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1 AND definition LIKE '%SearchText%'