如何用sql语句去掉表中字段自增标识：
--创建一个普通列
ALTER TABLE dbo.表名 ADD 列名 INT
GO
--将自增列数据插入新建列名中
UPDATE dbo.表名 SET 列名 = 自增列名;
GO
--删除自增列
ALTER TABLE dbo.表名 DROP COLUMN 自增列名
GO
--修改增加的列名为自增列的列明
EXEC sys.sp_rename @objname = N'表名.列名',
@newname = '自增列名',
@objtype = 'COLUMN'
一张表有外键或者主键时，如何用sql语句去掉表中字段自增标识
语句如下（多个主键，多个外键不行）：
--创建一个普通列
IF NOT EXISTS ( SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('Table_1') AND name = 'id1')
BEGIN
ALTER TABLE dbo.Table_1 ADD id1 INT
END
GO
IF EXISTS (SELECT COLUMNPROPERTY( OBJECT_ID('Table_1'),'id','IsIdentity'))
BEGIN
DECLARE @name NVARCHAR(100)--外键名
DECLARE @id NVARCHAR(100)--主键id
DECLARE @name1 NVARCHAR(100)--主键名
DECLARE @sql VARCHAR(MAX)
--删除外键
IF EXISTS ( SELECT name from sys.foreign_key_columns f join sys.objects o on f.constraint_object_id=o.object_id where f.parent_object_id=object_id('Table_1'))
BEGIN
SET @name= (SELECT name from sys.foreign_key_columns f join sys.objects o on f.constraint_object_id=o.object_id where f.parent_object_id=object_id('Table_1'))
SET @sql='alter table Table_1 drop constraint '+ @name
EXEC(@sql)
END
--删除主键
IF EXISTS ( SELECT CONSTRAINT_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='Table_1')
BEGIN
SELECT @name1=CONSTRAINT_NAME,@id=COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='Table_1'
END
SET @sql='Alter table Table_1 Drop Constraint '+ @name1
PRINT @sql
EXEC(@sql)
--将自增列数据插入新建列名中
UPDATE dbo.Table_1 SET id1 = id
--删除自增列
ALTER TABLE dbo.Table_1 DROP COLUMN ID
--修改增加的列名为自增列的列明
EXEC sys.sp_rename @objname = N'Table_1.id1',
@newname = 'id',
@objtype = 'COLUMN'
--重新增加主键
IF ISNULL(@name1,'')<>''
BEGIN
SET @sql='ALTER table Table_1 add CONSTRAINT '+ @name+' Primary Key('+@id+') '
EXEC(@sql)
END
--重新增加外键
IF ISNULL(@name,'')<>''
BEGIN
SET @sql='ALTER table Table_1 add CONSTRAINT '+ @name+' foreign key(id) references Table_2('+@id+') '
EXEC(@sql)
END
END
GO
--是否存在一个普通列
IF EXISTS ( SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('Table_1') AND name = 'id1')
BEGIN
ALTER TABLE dbo.Table_1 DROP COLUMN id1
END
GO
原创： https://www.cnblogs.com/summert/p/7000090.html