SQL Server – Identify Indexed Views

Background

Wanted to identify Indexed Views and took a look at sys.views to see if it exposes a guiding attribute.  But, No.

Code

Looked on the Net and found a good track.

Outline

  1. Look for views in sys.views
  2. Perform an inner join against sys.indexes

SQL

select 

          [object]
            = tblSS.[name]
              + '.'
              + tblSV.[name]

        , [index]
            = tblSI.[name]

        , [indexType]
            = tblSI.[type_desc]

from   sys.views tblSV

inner join sys.schemas tblSS

    on tblSV.schema_id = tblSS.schema_id

inner join sys.indexes tblSI

    on tblSV.object_id = tblSI.object_id

order by
        [object]
      , [index]

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s