求助：获取含包含列的索引定义的SQL查询无法正常运行

以下是调整后的查询语句，可正确获取所有包含包含列的索引定义：

WITH IndexInfo AS (
    SELECT 
        t.name AS TableName,
        i.name AS IndexName,
        STUFF((
            SELECT ',' + c.name
            FROM sys.index_columns ic
            JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
            WHERE ic.object_id = i.object_id 
            AND ic.index_id = i.index_id 
            AND ic.is_included_column = 0  -- 仅键列
            ORDER BY ic.key_ordinal
            FOR XML PATH('')), 1, 1, '') AS KeyColumns,
        -- 处理包含列为空的情况，返回空字符串而非NULL
        COALESCE(STUFF((
            SELECT ',' + c.name
            FROM sys.index_columns ic
            JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
            WHERE ic.object_id = i.object_id 
            AND ic.index_id = i.index_id 
            AND ic.is_included_column = 1  -- 仅包含列
            FOR XML PATH('')), 1, 1, ''), '') AS IncludedColumns
    FROM 
        sys.indexes i
    JOIN 
        sys.tables t ON i.object_id = t.object_id
    WHERE 
        i.is_primary_key = 0  -- 排除主键索引
        AND i.is_unique = 0    -- 排除唯一索引
)

SELECT 
    TableName, 
    IndexName, 
    KeyColumns, 
    IncludedColumns
FROM 
    IndexInfo
-- 筛选存在包含列的索引
WHERE IncludedColumns <> ''
ORDER BY 
    TableName, IndexName;

关键调整说明 #

• 移除了原查询中用于查找重复索引的WHERE EXISTS子句，转而筛选包含列不为空的索引，直接匹配你"获取所有包含包含列的索引定义"的需求
• 用COALESCE处理包含列字段，避免出现NULL值，让结果格式更统一
• 调整排序逻辑为按表名+索引名排序，结果更易读

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