List objects that use specific object in Azure SQL database

Article for: SQL Server Oracle database

Query below lists all objects that are referencing to specific object .

Query

select schema_name(obj.schema_id) +  '.' + obj.name
        + case when referenced_minor_id = 0 then ''
               else '.' + col.name end as referenced_object,
       'referenced by' as 'rel',
       schema_name(ref_obj.schema_id) as referencing_schema,
       ref_obj.name as referencing_object_name,
       case when ref_obj.type_desc = 'USER_TABLE' 
                 and dep.referencing_minor_id != 0
            then 'COLUMN'
            else ref_obj.type_desc end as referencing_object_type,
       ref_col.name as referencing_column
from sys.sql_expression_dependencies dep
join sys.objects obj
     on obj.object_id = dep.referenced_id
left join sys.columns col
     on col.object_id = dep.referenced_id
     and col.column_id = dep.referenced_minor_id
join sys.objects ref_obj
     on ref_obj.object_id = dep.referencing_id
left join sys.columns ref_col
     on ref_col.object_id = dep.referencing_id
     and ref_col.column_id = dep.referencing_minor_id
where schema_name(obj.schema_id) = 'schema name' -- provide schema name here
      and obj.name = 'object name'   -- provide object name here
order by referencing_schema,
         referencing_object_name;

Columns

  • referenced_object - provided schema name, object name and eventually automaticlly added column
  • ref - string 'referenced by'
  • referincing_schema - schema name of the referencing object
  • referencing_object_name - name of the referencing object
  • referencing_object_type - tpe of the referencing object
  • referencing_column - computed column name

Rows

  • One row represents one depending object
  • Scope of rows: all objects that are depending on specified object
  • Ordered by referencing object schema name and name

Sample results

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