Alex Rivera | Logout

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

Asked 2011-02-10T21:38:29.203
29

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.

sql-server t-sql sql-server-2008 metadata indexed-view
Edit
Report

1 Answer

28
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
answered 2011-02-10T21:45:47.553

Your Answer