If we ever get in a situation where we need to find a particular string in a stored procedure or trigger or any other database object, use the below script:
-- SQL 2000
select * from ( SELECT o.name, d.colid, o.type, convert(varchar(4000), c.text)
+ CASE d.colid WHEN 1 THEN '' ELSE convert(varchar(4000), d.text) END as LineText
FROM syscomments c, syscomments d, sysobjects o
WHERE c.id = d.id and c.id = o.id
and ( (c.colid = d.colid - 1) or (c.colid = d.colid and d.colid=1) ) ) a
where LineText like '%string%'
order by name, colid
-- SQL 2005
select * from sys.sql_modules
where definition like '%string%'
