Seeing as a foreign key does not automatically create an index in SQL Server, I want to create an explicit index on every FK field in my database. And I have over 100 tables in the schema...
So, does anyone have a ready packaged script that I could use to detect all FKs and create an index on each?
Ok, here is my take on this. I added support for schemes and also check if an index exists with the current naming convention. This way as you modify your tables you can check for missing 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 OK, I worked this out myself - here it is for everyone else's benefit...
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 doesn't work 100%, such as if you have two FKs on one table to the same primary table, but there are few enough instances of this (in my DB at least) that I could viably correct these by hand.
I modified the query to use they 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 
Seeing all of these answers is wonderful but I needed to ignore those foreign keys that already had indexes. To that end I had borrowed some code and modified it with some of the code here.
The major parts of this code really comes from: Identify all non indexed foreign keys
SELECT 'CREATE NONCLUSTERED INDEX ndx_' + FK.Table_Name + '__' + FK.Column_Name + ' ON [' + FK.Table_Name + '] (' + FK.Column_Name + ')' FROM ( SELECT Object_Name(a.parent_object_id) AS Table_Name ,b.NAME AS Column_Name FROM sys.foreign_key_columns a ,sys.all_columns b ,sys.objects c WHERE a.parent_column_id = b.column_id AND a.parent_object_id = b.object_id AND b.object_id = c.object_id AND c.is_ms_shipped = 0 EXCEPT SELECT Object_name(a.Object_id) ,b.NAME FROM sys.index_columns a ,sys.all_columns b ,sys.objects c WHERE a.object_id = b.object_id AND a.key_ordinal = 1 AND a.column_id = b.column_id AND a.object_id = c.object_id AND c.is_ms_shipped = 0 ) FK No idea if this is any more accurate but I found this while looking for similar https://encodo.com/latest/developer-blogs/create-indexes-for-all-foreign-keys-in-sql-server/ which generates a create-script similarly to some of the above, which should at least give more options to anyone ending up here to try things.
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Executing this script will generate a create-script for inserting indexes -- over all tables in the database. -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Select 'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].[' + tab.[name] + ']'') AND name = N''IX_' + cols.[name] + ''') ' + 'CREATE NONCLUSTERED INDEX [IX_' + cols.[name] + '] ON [dbo].[' + tab.[name] + ']( [' + cols.[name] + '] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]' From sys.foreign_keys keys Inner Join sys.foreign_key_columns keyCols On keys.object_id = keyCols.constraint_object_id Inner Join sys.columns cols On keyCols.parent_object_id = cols.object_id And keyCols.parent_column_id = cols.column_id Inner Join sys.tables tab On keyCols.parent_object_id = tab.object_id Order by tab.[name], cols.[name] 