List indexed (materialized) views in Azure SQL database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-04-01

Table of Contents:


    Article for: Azure SQL Database SQL Server Oracle database PostgreSQL

    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

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

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept