1

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.

Mike G
  • 4,232
  • 9
  • 40
  • 66
Chris Baxter
  • 16,083
  • 9
  • 51
  • 72

2 Answers2

2

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

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • My understanding that the NOEXPAND option didn't work with 2008; I know it worked with 2005; have you tested it with 2008? Microsoft had told me I would be out of luck... – Chris Baxter Jun 10 '11 at 05:22
  • @Calgary Coder: My MSDN link is for SQL Server 2008 and says "In SQL Server Enterprise, the query optimizer automatically considers the indexed view. To use an indexed view in all other editions, the NOEXPAND table hint must be used." – gbn Jun 10 '11 at 06:40
-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

LWestcott
  • 1
  • 2