Find objects used by specific stored procedure in Azure SQL database

Article for: SQL Server Oracle database IBM Db2

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

0
There are no comments. Click here to write the first comment.