批量重排标识列顺序

原贴发表在 http://community.csdn.net/Expert/TopicView1.asp?id=4296496

/*
    由于删除等操作,表中的identity列可能出现不连续的数字，这时可以重新对标识列排序
    基本思路:删除标识列，然后重建

    --vivianfdlpw 2005.9 引用请保留此信息
*/
exec sp_msforeachtable
       @command1='if exists(select 1 from syscolumns
                               where id=object_id(''?'')
                                     and autoval is not null)
                  begin
                     declare @name varchar(20)
                     select @name=name from syscolumns
                        where id=object_id(''?'') and autoval is not null
                     exec(''alter table ? drop column ''+@name)
                     exec(''alter table ? add ''+@name+'' int identity(1,1)'')
                 end
                '