邹老大，请回答！！！

[wg168]

请问如何用脚本去掉某个字段的IDENTITY属性！！！

[老本]

Not easy.

You could create a new column, populate it with the old data from
the IDENTITY column then change any referencing indexes, constraints, etc
and drop the old column.

Alternatively, if you remove the IDENTITY property using Enterprise
Manager's table designer it will drop and re-create the table for you. Not
something you want to do while the system is in use though.

[newqq]

學習

[fiele]

我认为建一个新的列是比较好的方法
邹老大的原代码写法太麻烦了

[gahade]

先建一个和原表一样结构的表,只是字段不是identity属性,然后将原表的数据都插到新表里,drop掉原表,将新表改名为原表名

[gahade]

邹老大的代码:

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)

[wg168]

我那个字段是主建。删除时候要抱错！！！
有没有其他方法！！！

[gahade]

1.create table 新表(id int,...) --id不是自增列,其它列同原表一样
2.insert into 新表(id,...)
select * from 原表
3.drop table 原表
4.exec sp_rename '新表','原表'

[中国风]

楼上正解

[Well]

BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_t1
(
a varchar(50) NULL,
b int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.t1)
EXEC('INSERT INTO dbo.Tmp_t1 (a, b)
SELECT a, b FROM dbo.t1 TABLOCKX')
GO
DROP TABLE dbo.t1
GO
EXECUTE sp_rename N'dbo.Tmp_t1', N't1', 'OBJECT'
GO
COMMIT