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

Question

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

我发现在开发过程中运行"ALTER VIEW"非常容易忽略我不仅正在编辑视图，还删除了现有索引。因此，我认为最好有一个小型实用程序查询，可以列出所有有索引的视图。

- EBarr

Answer 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
    

微软建议使用更新的SQL Server系统视图。以下是等效内容：

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

- Joe Stefanelli

Answer 2

我喜欢使用更新的系统表：

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
  )

内连接版本

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

- Michael J Swart

---

我建议使用内连接作为良好的实践（尽管在这种特定情况下，查询计划方面可能并不重要）。 - deutschZuid