Find a database objects dependencies in SQL Server.

declare @find as varchar(200);
set @find = '<OBJECT_NAME>';

set @find = '%' + @find + '%';
select distinct object_name(asm.OBJECT_ID) as [name],
    o.type_desc as [type]
from sys.all_sql_modules as asm
inner join sys.objects as o
    on o.object_id = asm.object_id
where definition like @find
and o.is_ms_shipped = 0
union
select distinct object_name(ID),
    o.type_desc
from syscomments as asm
inner join sys.objects as o
    on o.object_id = asm.ID
where TEXT like @find
    and o.is_ms_shipped = 0
    and o.name not like 'SP_%'
order by 1;