List tables used by Indexed (Materialized) Views

Article for: Azure SQL Database Oracle database

Query below lists indexed (materialized) views in a database with their references.


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


select distinct schema_name(v.schema_id) as schema_name, as view_name,
       schema_name(o.schema_id) as referenced_schema_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,


  • 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
    • VIEW


  • One row represents one reference
  • Scope of rows: all references from indexed view to other entities
  • Ordered by schema name, view name

Sample results

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.