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.

Cataract

Answer 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
    

Microsoft recommends using the newer SQL Server system views. Here is the equivalent:

SELECT 
    o.name as view_name, 
    i.name as index_name
FROM 
    sys.objects o 
    JOIN sys.indexes i ON o.object_id = i.object_id 
WHERE 
    o.type = 'V' -- View

Ruthenian

Answer 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

Rosas

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