Search code examples
sql-server-2008metadataindexed-view

Need query to determine if any Indexed Views exist in a database

We are in the process of migrating from SQL-Server 2005 Enterprise to SQL-Server 2008 Standard.

I am trying to find a query that can tell me if any INDEXED VIEWS exist in a database (as they won't be supported in SQL-Server 2008 Standard). Nothing stood out in the information schemas, and google isn't proving to be of much help.

Solution

  • Something like...

    SELECT * FROM 
sys.views v
JOIN
sys.indexes i On v.object_id = i.object_id

    or

    SELECT * FROM 
    sys.views v WHERE OBJECTPROPERTY(v.object_id, 'IsIndexed') = 1

    Indexed views are supported on standard edition with the NOEXPAND hint: DBA.SE and MSDN