用于查找和创建架构的缺失索引的脚本

-- 查找缺失索引
SELECT
    t.name AS TableName,
    c.name AS ColumnName,
    ic.index_id AS IndexID,
    ic.index_column_id AS IndexColumnID
FROM
    sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
LEFT JOIN sys.indexes i ON t.object_id = i.object_id
    AND i.is_primary_key = 0 -- 排除主键索引
    AND i.is_unique_constraint = 0 -- 排除唯一约束索引
LEFT JOIN sys.index_columns ic ON i.object_id = ic.object_id
    AND i.index_id = ic.index_id
    AND c.column_id = ic.column_id
WHERE
    i.object_id IS NULL -- 未创建索引
    AND t.is_ms_shipped = 0 -- 排除系统表
    AND t.name <> 'sysdiagrams' -- 排除特定表
ORDER BY
    t.name,
    c.name;

-- 创建缺失索引
-- CREATE INDEX [IndexName] ON [TableName] ([ColumnName]);