declare @TextSought varchar(100)

set @TextSought = 'ErrorMessage'

select distinct top 100 percent 	
    obj_type 	= o.type, 
    obj_name 	= o.name,
    parent_type = p.type,
    parent_name = p.name

from 
    sysobjects o
    inner join syscomments c
        on o.id = c.id
    left join sysobjects p
        on o.parent_obj = p.id

where 1=1
    and c.text like '%' + @TextSought + '%'
    and o.name <> @TextSought

order by 
    o.type, 
    o.name

create function dbo.CodeObjectsContaining(
    @TextSought varchar(100)
    ) returns table as return

--declare @TextSought varchar(100)
--set @TextSought = 'employee'


select distinct top 100 percent
     ObjType = o.type
    ,ObjDesc = o.type_desc
    ,ObjName = o.name
    ,ParentType = p.type
    ,ParentDesc = p.type_desc
    ,ParentName = p.name

from 
    sys.objects o

    inner join sys.sql_modules m
        on o.object_id = m.object_id

    left join sys.objects p
        on o.parent_object_id = p.object_id

where 1=1
    and m.definition like '%' + @TextSought + '%'
    and o.name <> @TextSought

order by
     o.type_desc
    ,o.name