在sql Server中看作
foreign key does not automatically create an index,我想在我的数据库中的每个FK字段上创建一个显式索引.我在模式中有超过100个表…
那么,有没有人有一个现成的打包脚本,我可以用来检测所有FK并在每个FK上创建一个索引？
解决方法
好的,这是我对此的看法.我添加了对方案的支持,并检查是否存在具有当前命名约定的索引.这样,在修改表时,可以检查缺失的索引.
SELECT 'CREATE NONCLUSTERED INDEX IX_' + s.NAME + '_' + o.NAME + '__' + c.NAME + ' ON ' + s.NAME + '.' + o.NAME + ' (' + c.NAME + ')'
FROM sys.foreign_keys fk
INNER JOIN sys.objects o ON fk.parent_object_id = o.object_id
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns c ON fkc.parent_object_id = c.object_id
AND fkc.parent_column_id = c.column_id
INNER JOIN sys.tables t ON t.object_id = o.object_id
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
LEFT JOIN sys.indexes i ON i.NAME = ('IX_' + s.NAME + '_' + o.NAME + '__' + c.NAME)
WHERE i.NAME IS NULL
ORDER BY o.NAME
