Search text in SPs/Function/etc.

select object_name(id) as object_name_, count(*) as count_
from syscomments where text like '%TEXT%'
group by object_name(id) order by object_name(id)

-- same select, including schema name:

select schema_name(o.schema_id) as schema_, o.name, count(*) as count_
from syscomments c join sys.objects o on c.id = o.object_id
where text like '%TEXT%'
group by o.name, schema_name(o.schema_id)
order by schema_name(o.schema_id), o.name

-- same select, including objects types:

select CASE WHEN type in('P', 'PC', 'RF', 'X') THEN 'SP'

WHEN type in('AF', 'FN', 'FS', 'FT', 'IF', 'TF') THEN 'function' 

WHEN type in('TA', 'TR') THEN 'trigger'  

ELSE 'other' 

END as objType, --o.type, 

schema_name(o.schema_id) as schema_, o.name, count(*) as count_ 

from syscomments c join sys.objects o on c.id = o.object_id 

where text like '%TEXT%' 

group by o.type, o.name, schema_name(o.schema_id) 

order by CASE WHEN type in('P', 'PC', 'RF', 'X') THEN 1

WHEN type in('AF', 'FN', 'FS', 'FT', 'IF', 'TF') THEN 2

WHEN type in('TA', 'TR') THEN 3

ELSE 10

END,

schema_name(o.schema_id), o.name

-- same select, including the object text:

select TT.objType, TT.schema_, TT.name,
Cast(( Select text As [text()]
From        sys.syscomments T1
Where        T1.ID=TT.ID
Order By colid
For XML Path('')) As XML) objText
FROM ( select c.id,
CASE WHEN type in('P', 'PC', 'RF', 'X') THEN 'SP'
WHEN type in('AF', 'FN', 'FS', 'FT', 'IF', 'TF') THEN 'function' 
WHEN type in('TA', 'TR') THEN 'trigger'  
ELSE 'other' 
END as objType, --o.type, 
schema_name(o.schema_id) as schema_, o.name, count(*) as count_ 
from syscomments c join sys.objects o on c.id = o.object_id 
where text like '%TEXT%' 
group by c.id,o.type, o.name, schema_name(o.schema_id) 
) TT
order by CASE WHEN TT.objType = 'SP' THEN 1
WHEN TT.objType = 'function' THEN 2
WHEN TT.objType = 'trigger' THEN 3
ELSE 10
END,
TT.schema_, 
TT.name