List tables used by Indexed (Materialized) Views

Article for: SQL Server Oracle database

Query below lists indexed (materialized) 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
join sys.indexes i 
     on v.object_id = i.object_id
     and i.type = 1
 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 indexed view to other entities
  • Ordered by schema name, view name

Sample results

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