sql修改库内表主键和自动标识

use master
go
if exists(select * from sysdatabases where name='mytest')
    drop database mytest
go
create database mytest
go
use mytest
go
create table user1
(
 mid int  ,
 name varchar(25)
)
go
create table user2
(
 mid int,
 name varchar(25)
)
go

select * from user1
select * from user2
--drop table  user1
--drop table  user2

--*****start*****--
DECLARE @table_name sysname
-- 获得所有User表
DECLARE tables_cursor CURSOR FOR select name from sysobjects where xtype='U' and status>0
 OPEN tables_cursor
 -- Perform the first fetch
 FETCH NEXT FROM tables_cursor INTO @table_name
 -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
 WHILE @@FETCH_STATUS = 0
 BEGIN
 -- 遍历表@table_name的每条记录
 -- 动态生成执行语句
 exec ( 'alter   table '+  @table_name+'   drop   column   mid '     )
 exec ('alter   table '+  @table_name +'  add   mid  int primary key  IDENTITY(1,1) ')
 FETCH NEXT FROM tables_cursor INTO @table_name
 END
 CLOSE tables_cursor  
 DEALLOCATE tables_cursor
--*****end*****--