Here's a great feature in SQL Server Management Studio SSMS 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 you 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!
Extras::::> Everything worked fine. Also what I need is script to reproduce the modification in our clients installations. and I got it!!!