SQL Server: Generating SQL For Missing Foreign Key Indexes

One way to improve performance in SQL Server is simply to ensure that there are indexes on all foreign key.  This is NOT done automatically, which is kind of surprising given how much of an impact such a change would have on the performance of a significant install base of SQL Server.

I have a simple stand-alone SQL Script that I execute to generate the SQL for the missing foreign key indexes. Here it is:

SELECT DISTINCT 'CREATE INDEX IX_' + REPLACE(objconstraint.name,'.','_') + ' ON ' + fkcols.name + '.' + objfk.name + '(' +
	STUFF((	select ',' + col.name
			from sys.foreign_key_columns fkcol2
			join sys.columns col
			on fkcol2.parent_object_id = col.object_id
			and fkcol2.parent_column_id = col.column_id
			and fkcol2.constraint_object_id=fkcol.constraint_object_id),1,1,'') + ');'
FROM sys.foreign_key_columns fkcol
	JOIN sys.objects objfk ON fkcol.parent_object_id = objfk.object_id
JOIN sys.schemas as fkcols ON fkcols.schema_id = objfk.schema_id
	JOIN sys.objects objconstraint ON fkcol.constraint_object_id = objconstraint.object_id
LEFT JOIN sys.index_columns ic ON ic.object_id = fkcol.parent_object_id
	AND ic.column_id = fkcol.parent_column_id
AND ic.index_column_id = fkcol.constraint_column_id
WHERE ic.object_id IS NULL

This script outputs the results in the results grid, so you can cut and paste it into a new query window for execution.

I was asked why I didn’t use INFORMATION_SCHEMA views in this bit of SQL. The answer is simple – the standard for INFORMATION_SCHEMA doesn’t include Indexes. Enjoy!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: