0

I have a table which should not have Identity property set on primary key column on multiple databases. May be some one has unintentionally set its property from design view.

Hence, I want to write a one time query which will detect and remove that identity specification from table.

I have read all the search articles and not satisfied with the solutions like creating new table, copying its metadata/data and renaming it since that table is very critical and column has many constraints over it.

I also tried, setting Identity_insert property ON but it is for one time only. When server is connected again, it does not allow me to save.

SET IDENTITY_INSERT <tableName> ON;

I don't want to change my backend procedures too by setting IDENTITY_INSERT ON before inserting data into it.

Any Suggestions?

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
rgCoder
  • 27
  • 1
  • 7
  • Duplicate also of https://stackoverflow.com/questions/702745/sql-server-how-to-drop-identity-from-a-column – Bergi Mar 25 '23 at 16:46

1 Answers1

1

Unfortunately there is no other way of dropping an IDENTITY property from a table without creating a dropping a recreating the table first. So you would need to store the data in a new table, drop the original table, and then rename the interim table to the correct name.

OR

You could just add a new column to your table, then copy your data into this new column, drop your identity column from the table and finally set your new column as a primary key.

How to find your IDENTITY columns...

select object_name(object_id) table_name,name column_name 
from sys.columns
where is_identity = 1
Thato
  • 128
  • 1
  • 7