How to search text of Triggers, Store Procedures and Functions?

Run the follow query in SqlServer and get result.

DECLARE @SEARCHSTRING VARCHAR(255)

SELECT @SEARCHSTRING = 'CREATE'

SELECT DISTINCT sysobjects.name AS [Object Name] ,
case when sysobjects.xtype = 'P' then 'Stored Proc'
when sysobjects.xtype = 'TF' then 'Function'
when sysobjects.xtype = 'TR' then 'Trigger'
end as [Object Type]
FROM sysobjects,syscomments
WHERE
sysobjects.id = syscomments.id AND
sysobjects.type in ('P','TF','TR')AND
sysobjects.category = 0
AND CHARINDEX(@SEARCHSTRING,syscomments.text)>0

You can get all textes of store procedures, functions and triggers from the follow query.

SELECT  *  FROM  syscomments