Menu
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.
652
You can use the sp_helpindex to view all the indexes of one table. And for all the indexes, you can traverse sys. objects to get all the indexes for each table. Only problem with this is that it only includes the index key columns, not the included columns.
Introduction to SQL Server indexed view To create an indexed view, you use the following steps: First, create a view that uses the WITH SCHEMABINDING option which binds the view to the schema of the underlying tables. Second, create a unique clustered index on the view. This materializes the view.
To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA: SELECT DISTINCT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA. STATISTICS WHERE TABLE_SCHEMA = 'your_schema'; Removing the where clause will show you all indexes in all schemas.
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 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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
