如何查询具有指定属性配置的SQL Server索引？

要查询符合你列出属性的索引，需要结合SQL Server的系统视图sys.indexes和sys.index_resumable_operations（针对可恢复索引的属性），以下是具体查询逻辑和语句：

关键属性对应系统字段说明 #

• SORT_IN_TEMPDB = ON：对应sys.indexes.sort_in_tempdb，值为1表示开启
• DROP_EXISTING = ON：该属性是创建/重建索引时的执行参数，不会持久化存储在系统元数据中。仅当索引处于正在执行的可恢复重建操作时，可通过sys.index_resumable_operations.drop_existing（值为1表示开启）获取；已完成的索引无法追溯该参数
• RESUMABLE = ON：对应sys.index_resumable_operations.is_resumable，值为1表示开启
• ONLINE = ON：已完成的索引可看sys.indexes.is_online_index_build（1表示开启）；正在执行的可恢复操作看sys.index_resumable_operations.is_online
• MAX_DURATION > 0 mins：对应sys.index_resumable_operations.max_duration（单位为分钟）
• MAXDOP <> 0：已完成的索引看sys.indexes.max_dop（0表示使用服务器默认，非0为指定值）；正在执行的可恢复操作看sys.index_resumable_operations.max_dop

查询语句 #

SELECT
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    o.name AS TableName,
    i.name AS IndexName,
    i.sort_in_tempdb AS SortInTempDB,
    ir.drop_existing AS DropExisting,
    ir.is_resumable AS Resumable,
    CASE WHEN ir.is_online IS NOT NULL THEN ir.is_online ELSE i.is_online_index_build END AS Online,
    ir.max_duration AS MaxDuration_Mins,
    CASE WHEN ir.max_dop IS NOT NULL THEN ir.max_dop ELSE i.max_dop END AS MaxDOP
FROM sys.indexes i
JOIN sys.objects o ON i.object_id = o.object_id
LEFT JOIN sys.index_resumable_operations ir ON i.object_id = ir.object_id AND i.index_id = ir.index_id
WHERE
    i.sort_in_tempdb = 1
    AND ir.drop_existing = 1 -- 仅能匹配正在执行的可恢复操作中开启DROP_EXISTING的情况
    AND ir.is_resumable = 1
    AND (ir.is_online = 1 OR i.is_online_index_build = 1)
    AND ir.max_duration > 0
    AND (ir.max_dop <> 0 OR i.max_dop <> 0)
    AND o.type = 'U' -- 仅筛选用户表
ORDER BY SchemaName, TableName, IndexName;

注意：由于DROP_EXISTING是执行时参数，上述查询仅能包含当前正在执行的、使用DROP_EXISTING=ON的可恢复索引操作；已完成的索引无法通过系统视图确认是否使用过该参数。

内容的提问来源于stack exchange，提问作者007