如何获取SQL服务器数据库中具有索引(即带有索引的视图)的视图列表?
我发现在开发过程中运行"ALTER VIEW"非常容易忽略我不仅正在编辑视图,还删除了现有索引。因此,我认为最好有一个小型实用程序查询,可以列出所有有索引的视图。
如何获取SQL服务器数据库中具有索引(即带有索引的视图)的视图列表?
我发现在开发过程中运行"ALTER VIEW"非常容易忽略我不仅正在编辑视图,还删除了现有索引。因此,我认为最好有一个小型实用程序查询,可以列出所有有索引的视图。
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
我喜欢使用更新的系统表:
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