How do you list all the indexed views in SQL Server?

Question

How can you get a list of the views in a SQL server database that have indexes (i.e. indexed views)?

I've found it's pretty easy to run an "ALTER VIEW" as I'm developing and overlook that I'm not only editing the view but also dropping an existing index. So I thought it would be nice to have a little utility query around that would list me off all the views with indexes.

Solution 1

SELECT o.name as view_name, i.name as index_name
    FROM sysobjects o 
        INNER JOIN sysindexes i 
            ON o.id = i.id 
    WHERE o.xtype = 'V' -- View

Solution 2

I like using the newer system tables:

select 
    OBJECT_SCHEMA_NAME(object_id) as [SchemaName],
    OBJECT_NAME(object_id) as [ViewName],
    Name as IndexName
from sys.indexes
where object_id in 
  (
    select object_id
    from sys.views
  )

The inner join version

select 
    OBJECT_SCHEMA_NAME(si.object_id) as [SchemaName],
    OBJECT_NAME(si.object_id) as [ViewName],
    si.Name as IndexName
from sys.indexes AS si
inner join sys.views AS sv
    ON si.object_id = sv.object_id

Comments

[deutschZuid]

I recommend using inner join as a good practice (though in this particular, it probably doesn't matter in terms of query plan).