Script to add an index on each foreign key?

Question

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?

Heh, I did one on the weekend and I discarded it. It was customised to my naming convention, so I did a lot of string manipulation and selected from sys.foreign_keys with schema_name(schema_id) and object_name(parent_object_id). — , Feb 22 '10 at 09:35

Shaul Behr · Accepted Answer · 2013-06-06T07:29:30.203

10

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.

edited Jun 06 '13 at 07:29

answered Feb 22 '10 at 10:04

Shaul Behr

36,951
69
249
387

In my DB this generated 0 rows, where as Scott's below did generate – Captain Prinny Feb 27 '20 at 18:48

score 9 · Answer 2 · answered May 28 '13 at 16:05

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

score 3 · Answer 3 · answered Aug 14 '12 at 17:04

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

This is nice, I'll add square braquets around the names just in case someone named a column user (just happened to me). — vercelli, Aug 29 '16 at 11:27

score 2 · Answer 4 · answered Mar 29 '20 at 04:02

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

score 0 · Answer 5 · answered Feb 27 '20 at 18:43

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]

Script to add an index on each foreign key?

5 Answers5