Finding Missing Indexes on Foreign Key Relation Tables
In this article, I'm exploring how to identify missing indexes in tables with foreign key relationships.
SELECT DB_NAME() AS DBName
, rc.Constraint_Name AS FK_Constraint
, ccu.Table_Name AS FK_Table
, ccu.Column_Name AS FK_Column
, ccu2.Table_Name AS ParentTable
, ccu2.Column_Name AS ParentColumn
, I.Name AS IndexName
, CASE WHEN I.Name IS NULL
THEN 'IF NOT EXISTS (SELECT * FROM sys.indexes
WHERE object_id = OBJECT_ID(N''' + RC.Constraint_Schema + '.' + ccu.Table_Name + ''')
AND name = N''IX_' + ccu.Table_Name + '_' + ccu.Column_Name + ''') ' + '
CREATE NONCLUSTERED INDEX IX_' + ccu.Table_Name + '_' + ccu.Column_Name + '
ON ' + rc.Constraint_Schema + '.' + ccu.Table_Name + '( ' + ccu.Column_Name + ' ASC )
WITH (PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,SORT_IN_TEMPDB = ON
,IGNORE_DUP_KEY = OFF
,DROP_EXISTING = OFF, ONLINE = ON);'
ELSE ''
END AS SQL
FROM information_schema.referential_constraints RC
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON rc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu2 ON rc.UNIQUE_CONSTRAINT_NAME = ccu2.CONSTRAINT_NAME
LEFT JOIN sys.columns c ON ccu.Column_Name = C.name
AND ccu.Table_Name = OBJECT_NAME(C.OBJECT_ID)
LEFT JOIN sys.index_columns ic ON C.OBJECT_ID = IC.OBJECT_ID
AND c.column_id = ic.column_id
AND index_column_id = 1
-- index found has the foreign key
-- as the first column
LEFT JOIN sys.indexes i ON IC.OBJECT_ID = i.OBJECT_ID
AND ic.index_Id = i.index_Id
WHERE I.name IS NULL
ORDER BY FK_table
,ParentTable
,ParentColumn;
