Code to Search SQL Server Objects for a Text String...

declare @SearchFor varchar(100);
set @SearchFor = 'blah blah blah';

if left(@SearchFor, 1) != '%' set @SearchFor = '%' + @SearchFor;
if right(@SearchFor, 1) != '%' set @SearchFor = @SearchFor + '%';

select
s.name as SchemaName
,o.name as ObjectName
,o.type_desc as ObjectTypeDesc
,o.is_ms_shipped as IsMSShipped
,'exec sp_helptext ''[' + s.name + '].[' + o.name + ']'';' as GetCode
from
sys.sql_modules sm
inner join sys.objects o
on sm.object_id = o.object_id
inner join sys.schemas s
on o.schema_id = s.schema_id
where
definition like @SearchFor -- What we are looking for
and o.is_ms_shipped = 0 -- Not a Microsoft bit of code
order by
s.name
,o.name;


Edit - 20101015: It's worth nothing that this is not the most efficient piece of code, and that there is a free RedGate product out there that will search your SQL instance for you (SSMS integrated):

http://www.red-gate.com/products/SQL_Search/