There's no way to remove an Identity Constraint from a column in a table, except through adding a new column, reassigning the Identity Column data to that new column then recreating the Identity Column (by drop and add) then reassigning back the data to it.
In the following code, I have a PrimaryKey ID that is also set as an Identity Column. What I want to do is to remove the Identity property by creating a new column name 'tempID' then temporarily storing the ID data into it while I recreate ID column so as to remove the Identity property.
ALTER TABLE [TableName]
ADD tempID int NULL
GO
UPDATE [TableName]
SET tempID=[IdentityColumnName]
GO
-- drop all foreign key constraints related to a table
-- see TSQL Script to drop all constraints
GO
ALTER TABLE [TableName]
DROP CONSTRAINT PK_[TableName]
GO
ALTER TABLE [TableName]
DROP COLUMN [IdentityColumnName]
GO
ALTER TABLE [TableName]
ADD [IdentityColumnName] int NULL
GO
UPDATE [TableName]
SET [IdentityColumnName]=tempID
GO
ALTER TABLE [TableName]
ALTER COLUMN [IdentityColumnName] int NOT NULL
GO
ALTER TABLE [TableName]
ADD CONSTRAINT PK_[TableName] PRIMARY KEY CLUSTERED ([IdentityColumnName]) ON [PRIMARY]
--or ADD CONSTRAINT [PrimaryKeyConstraintName]
GO
-- drop all foreign key constraints related to a table
-- see TSQL Script to drop all constraints
GO
ALTER TABLE [TableName]
DROP COLUMN tempID
GO
Posts
