Geek Answers Handbook

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

I wanted to change the column of the sql server 2005 table to IDENTITY (1,1)
By the way, this table is empty, and the column that needs to be changed is the primary key.

This column is also a foreign key for two other tables.

After googling, I found that you cannot use the Alter table syntax to modify a column and make it an indent column.
Reference # 1: How to Add an Identity Property to an Existing SQL Server Column

Link # 2: Adding an Identifier to an Existing SQL Server Column

I ended up checking dependent tables (2 of them) by deleting foreign keys (generated a script from SSMS) and then canceled the main table and then re-created with the identifier. (you can also try the renaming option)
Then the foreign keys are re-created for the earlier dependent two tables.

But it was all manual work, any scripts or SPs to make it easier.

Ideally, all these steps will be performed using script / tool / utility:

  • Checking the dependent keys of tables
  • Generate and delete external scripts for this
  • Create a script for the main table
  • delete the main table (or rename the table if the table has data)
  • recreate the table with the identity column enabled
  • re-create foreign keys
+5
3

SSMS script ( , script), , .

SSMS .. , , SSMS script, , , (, Red Gate ), .

: SSMS , ,

+4

( gbn post - .) (

, . , Microsoft - SSMS ( SMO?). script:

  • , ""

  • . .

  • / , , / " " " ".

  • script , , " Script". " ".

( - SSMS .) - , script on.

+2
  • pk
  • , .
  • reset

ALTER TABLE UserRole DROP CONSTRAINT PK_XX ALTER TABLE XX

ADD newX int not null (1,1)

update XX set newX = oldX

edit table XX DROP COLUMN oldX

this is the easiest way to set up an identity column. if you do not want to use a long generated script.

0
source

More articles:


All Articles