Script add index for each foreign key?

Question

An index in SQL Server is not automatically created as a foreign key; I want to create an explicit index for each FK field in my database. And I have more than 100 tables in the schema ...

So, does anyone have a pre-packaged script that I could use to detect all FKs and create an index on each?

Answer 1

Well, I worked it out myself - here it is for everyone else ...

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 

It does not work 100%, for example, if you have two FKs on the same table in the same primary table, but there are few of them (at least in my database), that I could fix them manually with confidence.

Answer 2

Well, here is my occupation. I added schema support, and also check if an index exists with the current naming convention. Thus, when changing tables, you can check for the absence of indexes.

  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 

Answer 3

I modified the request to use their system views. It will also script every FK in the table not just one.

 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