添加一个索引到每个外键的脚本?
添加一个索引到每个外键的脚本?
由于在SQL Server中,外键不会自动创建索引,因此我想在数据库中的每个外键字段上创建显式索引。而且我的模式中有超过100个表...\n所以,有没有人有一个现成的脚本,可以用来检测所有外键并在每个外键上创建索引?
这个问题的出现原因是为了在每个外键上添加索引,以提高查询性能。通过使用系统视图,可以更方便地获取外键的相关信息。下面提供了一个脚本来创建每个表中的所有外键的索引。
解决方法:
SELECT 'CREATE NONCLUSTERED INDEX ndx_' + o.name + '__' + c.name + ' ON ' + o.name + ' (' + c.name + ')' FROM sys.foreign_keys fk JOIN sys.objects o ON fk.parent_object_id = o.object_id JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id ORDER BY o.name
上述代码将为每个外键创建一个非聚集索引,并按表名排序。这样可以确保所有外键都被覆盖到。
为了避免可能出现的命名冲突问题,代码还在每个表和列名周围添加了方括号。这样,即使有人给列命名为"user",也不会导致语法错误。
通过使用上述脚本,可以方便地一次性为数据库中的所有外键添加索引,从而提高查询性能。
在上述代码中,问题的出现原因是没有为每个外键添加索引。这可能会导致查询性能下降,特别是在涉及到外键关联的查询时。为了解决这个问题,需要为每个外键添加一个索引。
以下是解决这个问题的方法:
1. 使用上述代码中的SELECT语句来生成创建索引的SQL语句。该代码使用了sys.foreign_keys、sys.objects、sys.foreign_key_columns、sys.columns、sys.tables和sys.schemas等系统视图来获取外键的相关信息。
2. 通过执行生成的SQL语句来创建索引。这将为每个外键创建一个非聚集索引。
3. 在修改表结构时,使用相同的SELECT语句来检查是否存在缺失的索引。如果出现缺失的索引,可以执行生成的SQL语句来创建它们。
通过执行上述步骤,可以确保为每个外键添加一个索引,从而提高查询性能并减少查询的响应时间。
问题的出现原因是用户需要给每个外键添加一个索引,但是现有的代码无法完全实现这个功能。为了解决这个问题,用户自己编写了一个脚本来为每个外键创建索引。脚本中的代码通过查询系统表来获取外键的相关信息,并生成创建索引的语句。然后,用户可以手动执行这些语句来为每个外键添加索引。
解决方法是使用以下代码来生成创建索引的语句:
select 'create index IX_'+c.name+'_'+p.name+' on '+c.name+'('+cf.name+');' from sysforeignkeys fk join sysobjects c on fk.fkeyid=c.id join sysobjects p on fk.rkeyid=p.id join syscolumns cf on c.id=cf.id and cf.colid = fk.fkey left join sysindexkeys k on k.id = cf.id and k.colid = cf.colid where k.id is null order by c.name
这段代码通过查询系统表来获取外键的相关信息,并使用字符串拼接的方式生成创建索引的语句。代码中使用了多个表的连接和条件判断来获取正确的外键信息。最后,使用`order by c.name`对结果进行排序。
但是,这段代码并不能完全实现添加索引的功能。如果一个表有多个指向同一个主表的外键,这段代码无法处理。用户在自己的数据库中没有碰到这种情况,所以可以手动修复这些问题。
对于用户的数据库而言,执行以上代码后得到的结果是0行。而Scott在下面的回答中给出的代码可以生成正确的结果。