List tables used by a view in Azure SQL database

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

Table of Contents:


    Query below lists views in a database with their references.

    Notes

    This query doesn't include cross database references. This query lists only references within database.

    Query

    select distinct schema_name(v.schema_id) as schema_name,
           v.name as view_name,
           schema_name(o.schema_id) as referenced_schema_name,
           o.name as referenced_entity_name,
           o.type_desc as entity_type
    from sys.views v
    join sys.sql_expression_dependencies d
         on d.referencing_id = v.object_id
         and d.referenced_id is not null
    join sys.objects o
         on o.object_id = d.referenced_id
     order by schema_name,
              view_name;
    

    Columns

    • schema_name - view schema name
    • view_name - view name
    • referenced_schema_name - schema of the referenced object
    • referenced_entity_name - name of the referenced object
    • entity_type - type of referenced entity
      • USER_TABLE
      • VIEW

    Rows

    • One row represents one reference
    • Scope of rows: all references from view to other entities
    • Ordered by schema name, view 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