如何在SQL Server中列出所有索引视图？

How can you get a list of the views in a SQL server database that have indexes (i.e. indexed views)?

如何在SQL服务器数据库中获取具有索引（即索引视图）的视图列表？

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.

我发现运行“ALTER VIEW”非常容易，因为我正在开发并忽略了我不仅要编辑视图而且还要删除现有索引。所以我认为有一个小的实用程序查询会很好，这会将所有带有索引的视图列出来。

2 个解决方案

#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

#2

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
  )