Find where specific table or view is used 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 list objects where specific table or view is used.

    Query

    select schema_name(o.schema_id) + '.' + o.name as [table],
           'is used by' as ref,
           schema_name(ref_o.schema_id) + '.' + ref_o.name as [object],
           ref_o.type_desc as object_type
    from sys.objects o
    join sys.sql_expression_dependencies dep
         on o.object_id = dep.referenced_id
    join sys.objects ref_o
         on dep.referencing_id = ref_o.object_id
    where o.type in ('V', 'U')
          and schema_name(o.schema_id) = 'Person'  -- put schema name here
          and o.name = 'Person'   -- put table/view name here
    order by [object]
    

    Columns

    • table - provided table/view schema name and name
    • ref - string 'is used by'
    • object - name of object with schema name which use specific procedure
    • object_type - type of found object

    Rows

    • One row represents one object
    • Scope of rows: all objects that are using provided table/view
    • Ordered by schema name, object name

    Sample Results

    List of objects that are using Person.Person table in AdventureWorks2017 database.

    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