如何编写查询来比较两个 SQL Server 表上的索引？

SELECT 
    i.name AS IndexName,
    i.type_desc AS IndexType,
    c.name AS ColumnName
FROM 
    sys.indexes i
INNER JOIN 
    sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN 
    sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE 
    i.object_id = OBJECT_ID('TableName1') -- 第一个表名
    AND i.type_desc <> 'HEAP' -- 排除堆表
ORDER BY 
    i.name, ic.key_ordinal

SELECT 
    i.name AS IndexName,
    i.type_desc AS IndexType,
    c.name AS ColumnName
INTO 
    #TempIndexInfo
FROM 
    sys.indexes i
INNER JOIN 
    sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN 
    sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE 
    i.object_id = OBJECT_ID('TableName2') -- 第二个表名
    AND i.type_desc <> 'HEAP' -- 排除堆表
ORDER BY 
    i.name, ic.key_ordinal

SELECT 
    IndexName,
    IndexType,
    ColumnName
FROM 
    #TempIndexInfo
EXCEPT
SELECT 
    IndexName,
    IndexType,
    ColumnName
FROM 
    #TempIndexInfo2

SELECT 
    IndexName,
    IndexType,
    ColumnName
FROM 
    #TempIndexInfo2
EXCEPT
SELECT 
    IndexName,
    IndexType,
    ColumnName
FROM 
    #TempIndexInfo