I was searching for the 2005 replace for syscomments and found this thread. I see it's fairly old, but figured I'd post the solution I've just worked up this afternoon. It seems to be working out well for me--fast and accurate as far as I tell.

You will need the use of a Numbers/Tally table:

CREATE PROCEDURE admin.pFindStringInCode

   @StringToFind nvarchar(50)

AS

SET NOCOUNT ON;



  SELECT  

    s.[name] AS SchemaName,

    o.[name] AS ObjectName,

    CAST(DENSE_RANK() OVER (ORDER BY s.[Name], o.[Name]) AS varchar) + '.' + 

    CAST(ROW_NUMBER() OVER (PARTITION BY s.[Name], o.[Name] ORDER BY s.[Name], o.[Name]) AS varchar) AS Instance,

    '...'+SUBSTRING(m.definition, CHARINDEX(@StringToFind, m.definition, n.Number), 100)+'...' AS Snippet 

  FROM

    sys.sql_modules AS m

    JOIN sys.objects AS o ON o.[object_id] = m.[object_id]

    JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id]

    CROSS JOIN admin.tbl_Numbers AS n

  WHERE 

   n.Number < LEN(m.definition) 

   AND SUBSTRING(m.definition, n.Number, LEN(@StringToFind)) = @StringToFind

   AND m.definition LIKE N'%'+ @StringToFind + N'%'

   

SET NOCOUNT OFF;

GO