declare @TableName nvarchar(250)

--声明读取数据库所有数据表名称游标mycursor1

declare mycursor1 cursor for select name from dbo.SysObjects WHERE OBJECTPROPERTY(ID, 'IsUserTable') = 1

open mycursor1

--从游标里取出数据赋值到我们刚才声明的数据表名变量中

fetch next from mycursor1 into @TableName

--如果游标执行成功

while (@@fetch_status=0)

begin

--声明读取数据表中所有int 不能为空 的字段 名称游标mycursor2

    declare @ColumnName nvarchar(250)

    --要修改的自增列

    declare mycursor2 cursor for select name from syscolumns Where ID=OBJECT_ID(N'['+@TableName+']') and name like ('%id%') and xtype=56 and isnullable=0

    --打开游标

    open mycursor2

    --从游标里取出数据赋值到我们刚才声明的数据字段变量中

    fetch next from mycursor2 into @ColumnName

    IF (CURSOR_STATUS('global','mycursor2')=1 )--游标的结果集至少有一行

    begin

    --如果游标执行成功

    while (@@fetch_status=0)

    begin

        --新增一列，把自增列值赋给新增列,删除自增列，修改改列名

        exec('alter table ['+@TableName+'] add temp_id int null')

        exec('update [DB_zabnew].[dbo].[zab_chaKan] set temp_id='+@ColumnName+'')

        exec('alter table ['+@TableName+'] drop column '+@ColumnName+'')

        exec sp_rename ''+@TableName+'.ck_id_id',@ColumnName,'column'

        --自己原来的做法，这样新加的列中就没有数据了。

        --先把自增列删除

        --exec ('ALTER TABLE ['+@TableName+'] DROP COLUMN '+@ColumnName+'')

        --再添加非自增列

        --exec ('ALTER TABLE ['+@TableName+'] alter column '+@ColumnName+' [int] NULL')

        fetch next from mycursor2 into @ColumnName

        end

    end

    --关闭游标

    close mycursor2

    --撤销游标

    deallocate mycursor2

 --用游标去取下一条记录

 fetch next from mycursor1 into @TableName

end

--关闭游标

close mycursor1

--撤销游标

deallocate mycursor1