SQLSERVER: How to alter an existing table int primary key to become an identity column?

Question

My database has a table with thousands of records. The primary key is an integer. There's a lot of foreign key constraints associated with this column.

I want to change this column to become an identity key. What's the best way to do it? I also need to send this update to our clients installations.

Bonus points for an answer that works in Sql Server 2000.

Answer 1

There's a great feature in SQL Server Management Studio that saved my day.

In SSMS go to Options -> Designers -> Table and Database Designers, check "Auto generate change scripts" and uncheck "Prevent saving changes that require table re-creation".

In object explorer, go to your table and select the column that will get the Identity specification. Right click and select modify. In the Column properties panel, expand the tree "Identity Specification" and change "(Is Identity)" to yes. Now on the upper left size, select the icon "Generate script". Pay attention to the warning messages.

Now you will have a generated script that will drop all your constraints, recreate the table with identity, and recreate the constraints. WOW!

I'll test it and post here my results.

Update: Everything worked fine. I forgot to say in the question that I need the script to reproduce the modification in our clients installations.

Answer 2

In Enterprise Manager, right click the table in table view, select design.

click the left hand side of the column (then, double click identity, in columns underneath, in column properties, turns it on, defaults to auto increment 1