每当外键上开创索引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.

图片 1

 

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

 

相关文章