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