since syscomments is nvarchar(4000) the word may be split into several rows.. so you concat them.. or at least pair them up... here is the sp I use to search on the stored procedures
CREATE PROCEDURE [dbo].[sp_FindInProc]
@sText NVARCHAR(MAX) = '%'
AS
BEGIN
SET NOCOUNT ON
SELECT DISTINCT obj.id as ID,obj.name AS Name
FROM
(
SELECT a.id,CAST(COALESCE(a.text,'') AS NVARCHAR(MAX)) + CAST(COALESCE(b.text,'') AS NVARCHAR(MAX)) AS [text]
FROM syscomments a LEFT JOIN
syscomments b ON a.colid+1 = b.colid and a.id = b.id
) AS comment
INNER JOIN sysObjects obj ON obj.id = comment.id
WHERE obj.type = 'P' AND comment.text like @sText
ORDER BY obj.Name
SET NOCOUNT OFF
END
