Code That Drops the IDENTITY Property from an Existing Column

-If other tables’ foreign keys point to the IDENTITY column, drop them. Here, the script drops the foreign key pointing to Orders2.OrderID from OrderDetails2.

ALTER TABLE OrderDetails2

DROP CONSTRAINT FK_OrderDetails2_Orders2

--If a primary key exists on the IDENTITY column, drop the primary key constraint the way this script drops the primary key from Orders2.

ALTER TABLE Orders2

DROP Constraint PK_Orders2

--Add another column with the same data type as the IDENTITY column to Orders 2 and allow NULLs.

ALTER TABLE Orders2

ADD new_OrderID int NULL

--Update the new column with the values of the IDENTITY column.

UPDATE Orders2

SET new_OrderID = OrderID

--If the new column doesn’t permit NULLs, alter the column to NOT NULL.

ALTER TABLE Orders2

ALTER COLUMN new_OrderID int NOT NULL

--Drop the IDENTITY column.

ALTER TABLE Orders2

DROP COLUMN OrderID

--Rename the new column to the dropped IDENTITY column’s name.

EXEC sp_rename 'Orders2.new_OrderID', 'OrderID', 'COLUMN'

--If a primary key exists on the new column, recreate the key. In this case you recreate the primary key on Orders2.

ALTER TABLE Orders2

ADD CONSTRAINT PK_Orders2 PRIMARY KEY(OrderID)

--On other tables, recreate any foreign keys that originally pointed to the old IDENTITY column and point them to the new column. Here, you recreate the foreign key on OrderDetails2.

ALTER TABLE OrderDetails2 WITH NOCHECK

ADD CONSTRAINT FK_OrderDetails2_Orders2

FOREIGN KEY(OrderID)

REFERENCES Orders2(OrderID)