Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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
like image 343
Binoj Antony Avatar asked Sep 02 '09 13:09

Binoj Antony


1 Answers

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

like image 190
gbn Avatar answered Sep 30 '22 01:09

gbn