sql-servert-sqlsql-server-2008metadataindexed-view

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


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

  • 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