Script to add an index on each foreign key?

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 o开发者_开发百科n each?

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.

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

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]