Converting Legacy Tables From Int Primary Keys To UniqueIdentifiers

If you have some legacy tables you where you want to convert from having a primary key of int to a primary key of uniqueidentifier, you need to do a lot of work.  Here are the steps:

1) Drop all forigen keys contraints that points to the table being modified.

2) Drop all views that reference the table being modified.

3) Drop all indexes that use the primary key or any forigen key column that points to the table being modified.

4) Rename all the old forigen key column that points to the table being modified, so that we can add a new column with the original name of type uniqueidentifier.

5) Drop the primary key contraints on the table being modified.

6) Add a new forigen key column to all tables where there was a forigen key column that points to the table being modified of the same name with a type of uniqueidentifier.  Make then NULL

7) Rename the primary key of the table being modified -- the int column.

8) Add a new column with the original primary key name of type of uniqueidentifier.

9) Fill all rows with the new primary key column using the function NewId()

10) Update all the new forigen key column with the new primary key using the old forigen key column bound to the old primary key on the new table.

11) Alter all the new forigen key column to NOT NULL where the original forigen key column was NULL.

12) Rebuild all indexes, including the primary key index on the new columns

13) Rebuild all forigen keys.

14) Rebuild all views.

Good Luck

{6230289B-5BEE-409e-932A-2F01FA407A92}

Comments

Popular posts from this blog

Yet once more into the breech (of altered programming logic)

How to convert SVG data to a Png Image file Using InkScape

Simple WP7 Mango App for Background Tasks, Toast, and Tiles: Code Explanation