在外键上创建索引V1.0

开始:

---

 

发现在一个项目中，后台数据库各个表，具有外键约束的列上都没有创建索引。我们需要一次性给他们创建索引，下面我写了一个通用的存储过程sp_CreateIndexForFK，来实现这一个功能。

也许有人问，为什么要在外键上创建索引，它有什么好处，可以参考：

FOREIGN KEY 约束：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 存储过程脚本

---

 

use master
go
if object_id('sp_CreateIndexForFK') Is not null
    Drop Proc sp_CreateIndexForFK
Go
/********************************************************************************************
%% Author        : Andy 
%% Create Date    : 2012-12-26
%% Description    : 在具有外键约束的列创建索引V1.0
********************************************************************************************/
create proc sp_CreateIndexForFK 
As
begin

    declare @sql                    nvarchar(4000)
            ,@parent_object            sysname
            ,@referenced_object        sysname
            ,@constraint_object_id    int
            ,@index_name            sysname
             

    declare cur_x cursor for 
        select a.object_id
                ,object_name(a.parent_object_id)    as parent_object
                ,object_name(a.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=a.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=a.parent_object_id
                                            and b.column_id=a.parent_column_id
                                    where a.constraint_object_id=@constraint_object_id
                                    for xml path('')
                                    ),1,1,'')
                    )+');'
                    
            exec sp_executesql @sql    
            
            print N'在表('+@parent_object+N')，已创建索引:'+@index_name 
                
        fetch next from cur_x into @constraint_object_id,@parent_object,@referenced_object
    end
    close cur_x
    deallocate cur_x 

end            
go
exec sp_ms_marksystemobject 'sp_CreateIndexForFK' --标识为系统对象
go


    

 

 

调用方法：

---

 

e.g.

 

注: sp_CreateIndexForFK存储过程，不适用于SQL Server 2000环境，已在 SQL Server 2005/2008R2/2012 测试通过.