List objects that use specific object in Azure SQL database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-04-01

Table of Contents:


    Article for: Azure SQL Database 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.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept