Finding a text inside a DB objects is a very difficult tasks. Example, if you want to find a specific text, we have open that all manually and find it. But below SQL statement will help us to find specified text inside the SPs, Functions and Views.

SELECT DISTINCT NAME AS [NAME],
 CASE  WHEN TYPE ='P' THEN 'PROCEDURE'
     WHEN TYPE IN('FN', 'IF','TF') THEN 'FUNCTION'
 END AS OBJECTTYPE
FROM SYSCOMMENTS as comm
JOIN sysobjects as obj
ON comm.id = obj.id and obj.type IN ('P','FN', 'IF', 'TF')
WHERE lower(TEXT) LIKE '%' + ltrim(rtrim(lower('texttosearch'))) + '%'