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

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.

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

There is no single "ALTER TABLE" DDL for changing an existing column to an identity column. You can only add a new identity column to an existing table.

This can be done in Enterprise Manager but you need to be aware that Sql server is creating a new table and copying you data across in the background. You may have some issues with this. Here is an article that explains this a bit more http://www.mssqltips.com/tip.asp?tip=1397

In your scenario i think you will need a combination of this script and something to disable and reenable your fk's.