Removing incremental identity integer datatype from a column

Question

Hi I want to remove the identity function of auto incrementing the primary key. Taking into consideration that this column is being referenced from several tables. I need the solution to be coded . Thanks for your help. Regards Tamer Khalil

naura pax · Answer

Find all foreign key contraints then write a .sql file having this script ALTER TABLE dbo.tableB DROP CONSTRAINT Fk_tableA_checked; -- col1 . --col2 . -- col3 . -- col4 --and so on. Then drop primary key constraint from your id ALTER TABLE dbo.tableA DROP CONSTRAINT pk_id; -- Then add a column of similar name and copy all data of original column to new one ALTER TABLE dbo.tableA Add id1 int update tableA set id1=id -- Then drop id column and rename id1 to id using sp_rename then set it to primary key using Alter Table Add constraint pk_id primary key (id). after that readd all your foreign key constraints. Alter table tableB Add constraint fk_id foreignkey (id) references tableA(id) hope i'm clear.