0

I am trying to edit a table structure. I have a table that contains a primary key and its identity specification is true. I want to change the primary key's identity specification to false using a query as long as I want to run this query on the user's program as I can't go for every user's PC and change it from the designer ... I just want a query to change it from identity specification true to false.

Can I use 

alter table table1 set table1ID INT NOT NULL PRIMARY KEY
a_horse_with_no_name
  • 497,550
  • 91
  • 775
  • 843
Bishoy Ezzat
  • 147
  • 3
  • 14

1 Answers1

3

Adding New Column First -

alter table tablename add columnname int

Then copying the data from identity to column to new column added using above query -

update tablename set columnname = identitycolumnname

Now Dropping identity column -

alter table tablename drop column identitycolumnname

Then finally renaming a new column inserted to a identity Column name -

EXEC sp_RENAME 'tablename.columnname' , 'identitycolumnname', 'COLUMN'

Krunal Shah
  • 806
  • 9
  • 24