Approach for altering Primary Key from GUID to BigInt in SQL Server related tables

Question

I have two tables with 10-20 million rows that have GUID primary keys and at leat 12 tables related via foreign key. The base tables have 10-20 indexes each.

We are moving from GUID to BigInt primary keys. I'm wondering if anyone has any suggestions on an approach. Right now this is the approach I'm pondering:

1. Drop all indexes and fkeys on all the tables involved.
2. Add 'NewPrimaryKey' column to each table
3. Make the key identity on the two base tables
4. Script the data change "update table x, set NewPrimaryKey = y where OldPrimaryKey = z
5. Rename the original primarykey to 'oldprimarykey'
6. Rename the 'NewPrimaryKey' column 'PrimaryKey'
7. Script back all the indexes and fkeys

Does this seem like a good approach? Does anyone know of a tool or script that would help with this?

TD: Edited per additional information. See this blog post that addresses an approach when the GUID is the Primary: http://www.sqlmag.com/blogs/sql-server-questions-answered/sql-server-questions-answered/tabid/1977/entryid/12749/Default.aspx

Answer 1

Your approach is how I would do it.

Do you really need bigint? a regular 4 byte int will go to 2 billion (2,147,483,647).

int, bigint, smallint, and tinyint