This is an updated version for SQL 2005 and later to search all code for a specific keyword
SELECT p.RoutineName,
'EXEC sp_helptext ' + QUOTENAME(p.RoutineName) AS [Exec]
FROM (
SELECT OBJECT_NAME(so.ID) AS RoutineName,
(SELECT TOP 100 PERCENT '' + sc.TEXT FROM SYSCOMMENTS AS sc WHERE sc.ID = so.ID ORDER BY sc.COLID FOR XML PATH('')) AS Body
FROM SYSOBJECTS AS so
WHERE so.TYPE IN ('C', 'D', 'FN', 'IF', 'P', 'R', 'RF', 'TF', 'TR', 'V', 'X')
) AS p
WHERE p.Body LIKE '%YourKeyWordHere%'
The types are
C = CHECK constraint
D = Default or DEFAULT constraint
FN = Scalar function
IF = In-lined table-function
P = Stored procedure
R = Rule
RF = Replication filter stored procedure
TF = Table function
TR = Trigger
V = View
X = Extended stored procedure