List indexed (materialized) views in SQL Server database

Query below lists indexed views, with their definition

Query

select schema_name(v.schema_id) as schema_name,
       v.name as view_name,
       i.name as index_name,
       m.definition
from sys.views v
join sys.indexes i
     on i.object_id = v.object_id
     and i.index_id = 1
     and i.ignore_dup_key = 0
join sys.sql_modules m
     on m.object_id = v.object_id
order by schema_name,
         view_name;

Columns

  • schema_name - schema name
  • view_name - indexed view name
  • index_name - name of the unique clustered index
  • definition - definition of the view

Rows

  • One row represents one indexed view in a database with its index
  • Scope of rows: all indexed views
  • 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.
0
There are no comments. Click here to write the first comment.