sql server change PK type from int to uniqueidentifier

Question

Hi! I need to change the type of my primary key column on a table from int to guid. The database already has data that I don't want to lose, and there are foreign keys to consider. Is there a painless way to do this or do I have to manually do it through a big a** script?:) I would appreciate any suggestions

Answer 1

You'll have to do it the hard way, using scripts:

0) get in single user mode
1) add the new GUID column to the main table, and populate it.
2) add the new FK column to each child table and populate them with an UPDATE FROM

UPDATE c
    SET FKcolumn=p.NewGuid
    FROM ChildTable             c
        INNER JOIN ParentTable  p ON p.OldIntID=c.OldIntId

3) drop the existing int FKs
4) drop the old int columns
5) add the new FKs on the guid column
6) get out of single user mode

you should be able to get SQL Server Management studio to generate the scripts for adding and dropping the columns and keys. Just make the changes in SSMS and click on the "Generate Change Script" toolbar icon and you can cut and paste the code to a text file.

Answer 2

Something that can help you is the following. This allows you to query tabels and columns, then walk through a manipulate them programmatically:

DECLARE @table varchar(100)

DECLARE csTables2 CURSOR FOR 
select distinct table_name from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE '%2'
OPEN csTables2

FETCH NEXT FROM csTables2 INTO @table

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'sp_RENAME ''' + @table + ''', ''' + REPLACE(@table, '2', '') + ''''
    --EXEC ('sp_RENAME ''' + @table + ''', ''' + REPLACE(@table, '2', '') + '''')
    FETCH NEXT FROM csTables2 INTO @table
END

CLOSE csTables2
DEALLOCATE csTables2