Find all objects containing specific text in SQL Server or all resource text

SELECT DISTINCT a.[name] FROM sysobjects a INNER JOIN syscomments b on a.id = b.id WHERE b.[text] LIKE '%yourtexttosearch%'