转老大的代码if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_DropIDENTITY]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[p_DropIDENTITY] GO/*--标识列转换为普通列 采用修改标识列名,然后按新规则重列的方法处理 只适用于标识列没有与其他对象有关联的表 转换后,列的相关对象也不会恢复 有一定的局限性,仅供参考--邹建 2005.05(引用请保留此信息)--*//*--调用示例 EXEC p_DropIDENTITY 'tb' --*/ CREATE PROC p_DropIDENTITY @TableName sysname --要处理的表名 AS IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@TableName),N'IsUserTable'),0)=0 BEGIN RAISERROR('"%s" 必须是当前数据库中已经存在的用户表',12,16,@TableName) RETURN END--标识列转换处理检查 DECLARE @s nvarchar(1000),@FieldName sysname,@bkFieldName sysname,@sql nvarchar(4000) SELECT @FieldName=QUOTENAME(c.name), @bkFieldName=CAST(NEWID() as char(36)), @s=@FieldName+N' '+QUOTENAME(t.name) +CASE WHEN t.name LIKE '%int' THEN N'' ELSE N'('+CAST(c.prec as varchar) +N','+CAST(c.scale as varchar)+N')' END FROM sysobjects o,syscolumns c,systypes t WHERE o.name=@TableName AND o.id=c.id AND c.xusertype=t.xusertype AND c.status=0x80 IF @@ROWCOUNT=0 BEGIN RAISERROR(N'表 "%s" 中无标识列',1,16,@TableName) RETURN END--修改标识列名 SET @sql=QUOTENAME(@TableName)+N'.'+@FieldName EXEC sp_rename @sql,@bkFieldName,N'COLUMN'--转换为标识列处理 SELECT @TableName=QUOTENAME(@TableName), @bkFieldName=QUOTENAME(@bkFieldName) EXEC('ALTER TABLE '+@TableName+N' ADD '+@s) EXEC('UPDATE '+@TableName+N' SET '+@FieldName+N'='+@bkFieldName+N' ALTER TABLE '+@TableName+N' DROP COLUMN '+@bkFieldName) RAISERROR(N'表 "%s" 中的标识列 "%s" 已经转换为普通列',1,16,@TableName,@FieldName) GO
ALTER TABLE t1 ADD ID_temp int GOUPDATE 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
晕,这样就得了 alter table 表 alter column 自增列名 int
不行,我那样改不行1 要么删除重建2 创建新表,插入数据,删除源表,重命名新表为源表的名字 BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET TRANSACTION ISOLATION LEVEL SERIALIZABLE COMMIT BEGIN TRANSACTION CREATE TABLE dbo.Tmp_t ( id int NOT NULL ) ON [PRIMARY] GO IF EXISTS(SELECT * FROM dbo.t) EXEC('INSERT INTO dbo.Tmp_t (id) SELECT id FROM dbo.t TABLOCKX') GO DROP TABLE dbo.t GO EXECUTE sp_rename N'dbo.Tmp_t', N't', 'OBJECT' GO COMMIT
identity列无法用代码直接取消,看看MS的解决方案 建立了张新表,把数据导过去,删除旧表,把新表改成旧表的名字BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION GO CREATE TABLE dbo.Tmp_Test1 ( T1 int NULL, T2 int NULL ) ON [PRIMARY] GO IF EXISTS(SELECT * FROM dbo.Test1) EXEC('INSERT INTO dbo.Tmp_Test1 (T1, T2) SELECT T1, T2 FROM dbo.Test1 WITH (HOLDLOCK TABLOCKX)') GO DROP TABLE dbo.Test1 GO EXECUTE sp_rename N'dbo.Tmp_Test1', N'Test1', 'OBJECT' GO COMMIT
drop procedure [dbo].[p_DropIDENTITY]
GO/*--标识列转换为普通列 采用修改标识列名,然后按新规则重列的方法处理
只适用于标识列没有与其他对象有关联的表
转换后,列的相关对象也不会恢复
有一定的局限性,仅供参考--邹建 2005.05(引用请保留此信息)--*//*--调用示例 EXEC p_DropIDENTITY 'tb'
--*/
CREATE PROC p_DropIDENTITY
@TableName sysname --要处理的表名
AS
IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@TableName),N'IsUserTable'),0)=0
BEGIN
RAISERROR('"%s" 必须是当前数据库中已经存在的用户表',12,16,@TableName)
RETURN
END--标识列转换处理检查
DECLARE @s nvarchar(1000),@FieldName sysname,@bkFieldName sysname,@sql nvarchar(4000)
SELECT @FieldName=QUOTENAME(c.name),
@bkFieldName=CAST(NEWID() as char(36)),
@s=@FieldName+N' '+QUOTENAME(t.name)
+CASE WHEN t.name LIKE '%int' THEN N''
ELSE N'('+CAST(c.prec as varchar)
+N','+CAST(c.scale as varchar)+N')'
END
FROM sysobjects o,syscolumns c,systypes t
WHERE o.name=@TableName
AND o.id=c.id
AND c.xusertype=t.xusertype
AND c.status=0x80
IF @@ROWCOUNT=0
BEGIN
RAISERROR(N'表 "%s" 中无标识列',1,16,@TableName)
RETURN
END--修改标识列名
SET @sql=QUOTENAME(@TableName)+N'.'+@FieldName
EXEC sp_rename @sql,@bkFieldName,N'COLUMN'--转换为标识列处理
SELECT @TableName=QUOTENAME(@TableName),
@bkFieldName=QUOTENAME(@bkFieldName)
EXEC('ALTER TABLE '+@TableName+N' ADD '+@s)
EXEC('UPDATE '+@TableName+N' SET '+@FieldName+N'='+@bkFieldName+N'
ALTER TABLE '+@TableName+N' DROP COLUMN '+@bkFieldName)
RAISERROR(N'表 "%s" 中的标识列 "%s" 已经转换为普通列',1,16,@TableName,@FieldName)
GO
GOUPDATE 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
alter table 表
alter column 自增列名 int
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_t
(
id int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.t)
EXEC('INSERT INTO dbo.Tmp_t (id)
SELECT id FROM dbo.t TABLOCKX')
GO
DROP TABLE dbo.t
GO
EXECUTE sp_rename N'dbo.Tmp_t', N't', 'OBJECT'
GO
COMMIT
建立了张新表,把数据导过去,删除旧表,把新表改成旧表的名字BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Test1
(
T1 int NULL,
T2 int NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Test1)
EXEC('INSERT INTO dbo.Tmp_Test1 (T1, T2)
SELECT T1, T2 FROM dbo.Test1 WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Test1
GO
EXECUTE sp_rename N'dbo.Tmp_Test1', N'Test1', 'OBJECT'
GO
COMMIT
?
1.创建同原表结构一样的新表,只是将自增改为非自增
2.将原来数据插入到新表
3.删除原表
4.将新表更名为原表表名
1.创建同原表结构一样的新表,只是将自增改为非自增
2.将原来数据插入到新表
3.删除原表
4.将新表更名为原表表名
-----------------------------
还不如建一个新列,将自增列内容导过来呢?
为何要新建一个表呀,多些一举