Script to index all foreign key columns for

Depending on your database design and number of rows in the table a possibly good performance improvement is to index all columns that refer to a primary key so that both ends of the relationship are indexed.  This script takes the grunt work out of it by using the database schema to find those columns and then index them.

I have tried to comment it to explain how it works. If anyone know where the monster SQL statement comes from please let me know and I will credit them accordingly.

Hope that helps, Jon Bosker, DB Gurus, 26 Nov 2015



--------------------------------------------------------------
-- Script to create indexes on all columns that have a foreign key constraint
-- note that this only works for single column foreign keys (all we use)
--------------------------------------------------------------
-- create a table variable to put the SQL commands for subsequent running:
DECLARE @t TABLE
(
SQLCommand varchar(8000),
id int identity(1,1)
)

-- This monster select gets a list of all columns that have a foreign key
-- (sorry I cannot remember where I got it from originally) 
INSERT INTO @t (SQLCommand)
SELECT DISTINCT
'CREATE NONCLUSTERED INDEX [IX_' + CU.COLUMN_NAME + '] ON [' + FK.TABLE_NAME + '] (['+CU.COLUMN_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]' AS [IndexCommand]
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU  ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT
i1.TABLE_NAME,
i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE
i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
  ) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
LEFT JOIN (
SELECT I.*
FROM sys.indexes I
JOIN sys.objects O ON I.object_id = O.object_id 
) INX 
ON INX.name LIKE '%' + PK.TABLE_NAME + '%' and INX.type_desc = 'NONCLUSTERED' AND INX.name LIKE 'IX%'
WHERE INX.name IS NULL     


-- Now we just need to run it line by line
DECLARE @sSQL varchar(8000)
DECLARE @counter int
SET @counter = 1


WHILE EXISTS(SELECT * FROM @t WHERE id >= @counter)
BEGIN
SELECT @sSQL = SQLCommand  FROM @t WHERE id = @counter
PRINT @sSQL
EXEC (@sSQL)  -- comment out this line to test it
SET @counter = @counter + 1
END