Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Generate index on foreign key columns

By tklimczak, 2012/03/29

This scrip can be usefull to automaticy create missing index on Fkey's.

This script is very easy to change to:

Present all (or missing) primary and foreign key and indexes in database,
Check if index name are consistent with code standard

	-- declare memory table

	DECLARE @INDEX_TABLE TABLE(
		primary_key INT IDENTITY(1,1) NOT NULL, 
		schema_name NVARCHAR(100), 
		table_name NVARCHAR(100), 
		column_name NVARCHAR(100),
		new_index_name NVARCHAR(100)
	)

	
	-- feed memory table by all foreign key without index in database
	INSERT INTO @INDEX_TABLE
		SELECT
			S.name as [Schema name],
			object_name(T.object_id) AS [Table name],
			C.name AS [Column name],
			''
		FROM 
			sys.columns C
			LEFT JOIN sys.tables T ON (C.object_id = T.object_id)
			LEFT JOIN sys.schemas S ON (S.schema_id = T.schema_id)
			LEFT JOIN sys.foreign_key_columns FKC ON (FKC.parent_object_id = C.object_id AND C.column_id = FKC.parent_column_id)
			LEFT JOIN sys.foreign_keys FK ON (FKC.constraint_object_id = FK.object_id)

			LEFT JOIN sys.index_columns IC ON (IC.object_id = C.object_id AND IC.column_id = C.column_id)	
			LEFT JOIN sys.indexes I ON (I.index_id = IC.index_id  AND I.object_id = C.object_id)
		WHERE 
			T.object_id is not null
			AND FK.name IS NOT NULL
			AND I.name IS NULL
		ORDER BY 
			S.name,
			object_name(T.object_id),
			C.name 



	DECLARE @loop_counter INT
	DECLARE @item_counter INT

	SET @loop_counter = ISNULL((SELECT COUNT(*) FROM @INDEX_TABLE),0)
	SET @item_counter = 1

	DECLARE @schema_name VARCHAR(100)
	DECLARE @table_name VARCHAR(100)
	DECLARE @column_name VARCHAR(100)
	DECLARE @query NVARCHAR(1000)
	DECLARE @index_name VARCHAR(200)

	WHILE @loop_counter > 0 AND @item_counter <= @loop_counter
	BEGIN
			
		-- get one row from memory table
			SELECT 
				@schema_name = schema_name, 
				@table_name = table_name,
				@column_name = column_name
			FROM 
				@INDEX_TABLE
			WHERE 
				primary_key = @item_counter

		
	-- prepare query
	SET @index_name = 'IX_' + @table_name + '_' + @column_name
	SET @query = 'CREATE NONCLUSTERED INDEX [' + @index_name + '] ON ['+ @schema_name+ '].[' + @table_name + '] 
		(
			[' + @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]
		'
	
	EXEC sp_executesql @query

	UPDATE @INDEX_TABLE 
	SET 
		new_index_name = @index_name
	WHERE 
		primary_key = @item_counter

		SET @item_counter =  @item_counter + 1
	END

	-- present all FKey's with new index
	SELECT 
		schema_name, 
		table_name, 
		column_name,
		new_index_name
	FROM 
		@INDEX_TABLE