How to find all stored procedures which contains a word in SQL Server

You can use the following query

SELECT DISTINCT OBJ.name AS [ProcedureName],OBJ.type_desc AS [Description]
FROM sys.sql_modules MOD
INNER JOIN sys.objects OBJ
ON MOD.object_id = OBJ.object_id
WHERE MOD.definition Like '%TYPE_WORD_HERE%' ESCAPE '\'