Finding Dependent Objects Information

select distinct

    Obj_Name = schema_name(o1.schema_id) + '.' + o1.name

    ,Obj_Type = o1.type_desc

    ,Dep_Obj_Name = schema_name(o2.schema_id) + '.' + o2.name

    ,Dep_Obj_Type = o2.type_desc

from sys.sysdepends sd

join sys.objects o1

 on sd.id = o1.object_id

join  sys.objects o2

 on sd.depid = o2.object_id

where (o1.type in ('V','U','P','TF','IF','FN','TR') and o2.type IN('V','U','P','TF','IF','FN','TR'))

and o1.type='P'

and

(

o1.schema_id = schema_id ('')

--and o1.name like ''

)

order by Obj_Name