日期:2014-05-18 浏览次数:20706 次
--删除主健 select 'alter table '+quotename(a.name)+' drop constraint ' +QUOTENAME(b.Name) from sys.objects as a inner join sys.objects as b on a.object_id=b.parent_object_id and b.type='PK' where not exists(select 1 from sys.columns where object_id=a.object_id and is_identity=1) and a.type='U' --新增标识列为主健,列名为ID,首先要定义好列名的唯1 select 'alter table '+quotename(name)+' add ID int identity(1,1) primary key' from sys.objects as a where not exists(select 1 from sys.columns where object_id=a.object_id and is_identity=1) and type='U'
------解决方案--------------------
--创建测试表 CREATE TABLE t1(ID int IDENTITY,A int) GO --插入记录 INSERT t1 VALUES(1) GO --1. 将IDENTITY(标识)列变为普通列 ALTER TABLE t1 ADD ID_temp int GO UPDATE t1 SET ID_temp=ID ALTER TABLE t1 DROP COLUMN ID EXEC sp_rename N't1.ID_temp',N'ID',N'COLUMN' INSERT t1 VALUES(100,9) GO --2. 将普通列变为标识列 CREATE TABLE t1_temp(ID int,A int IDENTITY) SET IDENTITY_INSERT t1_temp ON INSERT t1_temp(ID,A) SELECT * FROM t1 SET IDENTITY_INSERT t1_temp OFF DROP TABLE T1 GO EXEC sp_rename N't1_temp',N't1' INSERT t1 VALUES(109999) GO --显示处理结果 SELECT * FROM t1 /*--结果: ID A ----------------- ----------- 1 1 100 9 109999 10 --*/
------解决方案--------------------
大量的应该就需要动态拼接了。
------解决方案--------------------
搞那么复杂干嘛
直接图形界面改就是了
------解决方案--------------------