sql server helper stored procedure or utility for alter table alter column IDENTITY(1,1)

Question

I wanted to modify a column in a sql server 2005 table to IDENTITY(1,1)
Incidentally this table is empty and the column to be changed is a primary key.
This column is also a foreign key for two other tables.

After googling I found that you cannot use Alter table syntax to modify a column and make it an indentity column.
Link #1 : How do I add the identity property to an existing column in SQL Server
Link #2 : Adding an identity to an existing column -SQL Server

I ended up checking the dependent tables (2 of them) removing the foreign keys (generated the script from SSMS) then dropping the main table then re-creating with identity. (could try the rename option here as well)
Then re-created the foreign keys for the earlier dependent two tables.

But all this was manual work, any scripts or SPs out there to make this easier.

Ideally all these steps would be done by such a script/tool/utility:

1. Check dependent tables keys
   
2. Generate Create and drop foreign key scripts for this
   
3. Generate create script for the main table
   
4. drop the main table (or rename the table if the table has data)
   
5. re-create the table with identity column enabled
   
6. re-create foreign keys
   

Answer 1

You can use SSMS to generate a script (Edit a table, save script), but otherwise it's a manual process as you identified.

The SSMS scripts will pick up dependencies etc. For this kind of work, I tend to use SSMS to generate a basic script, pimp it a bit, run it carefully, then use a comparison tool (such as Red Gate compare) to generate a safer version.

Edit: The SSMS error is not an error, it's a safety check that can be switched off

Answer 2

(This is merely a follow-up to gbn's post with more details -- it isn't all that easy to figure this stuff out.)(

It isn't impossible to write a utility to do this, just very complex and very hard. Fortunately, Microsoft has already done it -- its called SSMS (or SMO?). To generate such a script:

• In the Object Explorer, drill down to the database and table that you want to modify Right click and select Design

• Make the desired changes to the one table in the design screen. It's reasonably intuitive.

• To add/remove the identity property, select the column in the upper pane, and in the lower pane/"Column Properties" tab, expand and configure the settings under "Identity Specification".

• To generate a script to implement all your changes, incorporating all the dependent key changes, click on the "Generate Change Script" toolbar button. This is also an option under the "Table Designer" menu.

I also do this to generate scripts (that I later modify--SSMS doesn't always produce the most efficient code.) Once done, you can exit out without saving your changes -- leaving you a DB you can test your new script on.

Answer 3

What's wrong with:

DBCC CHECKIDENT (yourtable, reseed, 10000)

?