Retrieve the database objects contain a specific text string

SELECT DISTINCT so.name, so.xtype, sc.TEXT

FROM syscomments sc

INNER JOIN sysobjects so ON sc.id=so.id

WHERE sc.TEXT LIKE '%<<search text string>>%'

AND so.xtype IN ('TR', 'FN', 'P', 'V', 'IF', 'TF', 'FS', 'FT')

ORDER BY so.xtype, so.name