List indexed (materialized) views in SQL Server database

Query below lists indexed views, with their definition


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


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


  • 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.
There are no comments. Click here to write the first comment.