Query below return all stored procedures and objects used by them.

Query

select schema_name(obj.schema_id) as schema_name,
       obj.name as procedure_name,
       schema_name(dep_obj.schema_id) as referenced_object_schema,
       dep_obj.name as referenced_object_name,
       dep_obj.type_desc as object_type
from sys.objects obj
left join sys.sql_expression_dependencies dep
          on dep.referencing_id = obj.object_id
left join sys.objects dep_obj
          on dep_obj.object_id = dep.referenced_id
where obj.type in ('P', 'X', 'PC', 'RF')
    --  and obj.name = 'procedure_name'  -- put procedure name here
order by schema_name,
         procedure_name;

Columns

• schema_name - schema name
• procedure_name - procedure name
• referenced_object_schema - schema name of the referenced object
• referenced_object_name - name of the referenced object
• object_type - type of referenced object

Rows

• One row represents one referenced object by procedure or only procedure if procedure is not using any objects
• Scope of rows: all objects that are used by procedure in database
• Ordered by schema name and procedure name

Sample Result