Create an index on the foreign key V1.0

Start:

 

In a project, no index is created for all the tables in the background database and columns with foreign key constraints. We need to create indexes for them at one time. Below I write a general Stored Procedure sp_createindexforfk to implement this function.

Some people may ask why an index should be created on the foreign key. For details, refer:

Foreign key constraint: http://msdn.microsoft.com/zh-cn/library/ms175464%28v= SQL .105%29.aspx

The benefits of indexing Foreign keys: http://www.sqlperformance.com/2012/11/t-sql-queries/benefits-indexing-foreign-keys

 

Sp_createindexforfk Stored Procedure script

 

Use mastergoif object_id ('SP _ createindexforfk ') is not null drop proc sp_createindexforfkgo /********************************* **************************************** * ******************* % Author: andy % create Date: 2012-12-26% description: create an index for a column with foreign key constraints: V1.0 ****************************** **************************************** * ********************/create proc sp_createindexforfk asbegin declare @ SQL nvarchar (4000 ), @ parent_object sysname, @ referenced_object sysname, @ constraint_object_id int, @ index_name sysname declare cur_x cursor for select. object_id, object_name (. parent_object_id) as parent_object, object_name (. referenced_object_id) as referenced_object from sys. foreign_keys A where exists (select 1 from sys. foreign_key_columns x left join sys. index_columns y on Y. object_id = x. parent_object_id and Y. column_id = x. parent_column_id where Y. index_id is null and X. constraint_object_id =. object_id) order by parent_object open cur_x fetch next from cur_x into @ constraint_object_id, @ parent_object, @ referenced_object while @ fetch_status = 0 begin set @ index_name = 'ix _ '+ @ parent_object +' _ '+ @ referenced_object set @ SQL = 'create nonclustered index [' + @ index_name + '] on' + quotename (@ parent_object) + '(' + (select stuff (select ',' + quotename (B. name) from sys. foreign_key_columns a inner join sys. columns B on B. object_id =. parent_object_id and B. column_id =. parent_column_id where. constraint_object_id = @ constraint_object_id for XML Path (''), 1, 1,'') + '); 'exec sp_executesql @ SQL print n' in the table ('+ @ parent_object + N'), you have created an index:' + @ index_name fetch next from cur_x into @ constraint_object_id, @ parent_object, @ referenced_object end close cur_x deallocate cur_x end goexec sp_ms_marksystemobject 'SP _ createindexforfk '-- identify as a system object go

 

 

Call method:

 

E.g.

 

Note: Sp_createindexforfk stored procedure, which is not applicable to the SQL Server 2000 environment and has passed the test on SQL Server 2005/2008 R2/2012.