Query to get dependent objects

-- Get SP names which references the tables with specified schema

SELECT DISTINCT sd.object_id "object_id",

OBJECT_NAME(sd.object_id)
"OBJECT_NAME",

OBJECT_NAME(referenced_major_id)
"Ref_OBJECT_NAME", referenced_major_id "Ref_OBJECT_Id"

into #table1

FROM sys.sql_dependencies sd

where referenced_major_id in

(

select object_id

from sys.tables

where schema_name(schema_id) = 'dbo'

)order by Ref_OBJECT_Id

-- Now, get sps with all dependent tables.

SELECT distinct sd.object_id "object_id",

OBJECT_NAME(sd.object_id)
"OBJECT_NAME",

OBJECT_NAME(referenced_major_id)
"Ref_OBJECT_NAME", referenced_major_id "Ref_OBJECT_Id"

FROM sys.sql_dependencies sd

where OBJECT_NAME(sd.object_id)

in

(

select [object_name] from #table1

)

drop table #table1