It’s good practice to ensure all foreign key columns are indexed (I’ve often wondered why SQL Server Management Studio doesn’t have a built-in option to perform this). I can’t remember where I saw this code originally (despite trying to search for a reference to it), but it is a useful addition to a TSQL code snippet toolbox:
--
-- Create non-clustered indexes for all foreign keys in the current database
--
DECLARE @sql varchar(4000)
CREATE TABLE #sqlfkindexes
(
id int not null identity(1,1),
sql varchar(4000) not null
)
INSERT INTO #sqlfkindexes
SELECT 'CREATE NONCLUSTERED INDEX [NC_' + tablename + '_' + columnname +'] ON [' +
schemaname + '].[' + tablename + '] ([' + columnname + ']);' as [sql]
FROM
(
-- All foreign key columns
SELECT
o.name AS tablename,
col.name AS columnName,
sch.name AS schemaname
FROM
sys.foreign_key_columns fkc
INNER JOIN sys.objects o ON fkc.parent_object_id = o.object_id
INNER JOIN sys.columns col ON col.object_id = o.object_id and fkc.parent_column_id = col.column_id
INNER JOIN sys.schemas sch ON sch.schema_id = o.schema_id
EXCEPT
-- All the already indexed columns
SELECT
o.name AS tablename,
col.name AS columnName,
sch.name AS schemaname
FROM
sys.index_columns ixc
INNER JOIN sys.objects o ON ixc.object_Id = o.object_id
INNER JOIN sys.columns col ON col.object_id = o.object_id and ixc.column_id = col.column_id
INNER JOIN sys.schemas sch ON sch.schema_id = o.schema_id
) FK
ORDER BY
schemaname,
tablename,
columnname
DECLARE @count int
SELECT @count = COUNT(*) FROM #sqlfkindexes
WHILE @count > 0
BEGIN
SELECT @sql = [sql] from #sqlfkindexes where [id] = @count
SET @count = @count - 1
PRINT @sql
-- Uncomment to execute directly
-- EXEC sp_executesql @sql
END
drop table #sqlfkindexes
GO
As is, this script prints out the missing index definitions but does not run them into the database. Simply copy and paste into another SSMS query window and run. (You could also uncomment the execute line, but I prefer to check them before running in).