Search This Blog

Saturday, March 05, 2016

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

Here's a great feature in SQL Server Management Studio SSMS that saved my day.
In SSMS 
  1. Go to Options -> Designers -> Table and Database Designers,
  2. Check "Auto generate change scripts" and uncheck "Prevent saving changes that require table re-creation".

In object explorer
  1. Go to you table and select the column that will get the Identity specification. Right click and select modify. 
  2. 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". 
  3. 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!!!