Geek Answers Handbook

Sql server changes PK type from int to uniqueidentifier

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

+5
source share
2 answers

You will have to do this in a complicated way using scripts:

0) enter single-user mode
1) add a new GUID column to the main table and fill it.
2) add a new FK column to each child table and populate them with UPDATE FROM

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

3) int FKs
4)

5) FK guid

6)

SQL Server Management . SSMS " Script", .

+5
  • " " , "DROP/CREATE CONSTRAINT INDEXES" ( ). SQL script, .

  • :

    CREATE FUNCTION [dbo].[GuidFromHash]
(
    @Input nvarchar(MAX)
)
RETURNS nvarchar(MAX)
AS
BEGIN
    RETURN LOWER(SUBSTRING(@Input, 1,8)+'-'+SUBSTRING(@Input, 9,4)+'-'+SUBSTRING(@Input, 13,4)+'-'+SUBSTRING(@Input, 17,4)+'-'+SUBSTRING(@Input, 21,12))
END

CREATE PROCEDURE [dbo].[bigIntToGuid]
(
    @table varchar(50),
    @column varchar(50)
)
AS
DECLARE @SQL VARCHAR(MAX)

SET @SQL='UPDATE @Table SET @Column=dbo.HashToGuid(''cc''+CONVERT(VARCHAR, HASHBYTES(''MD5'',LTRIM(@Column)),2))'
SET @SQL=REPLACE(@SQL,'@Table',@Table)
SET @SQL=REPLACE(@SQL,'@Column',@Column)
EXEC(@SQL)

SET @SQL='SELECT * FROM @Table'
SET @SQL=REPLACE(@SQL,'@Table',@Table)
SET @SQL=REPLACE(@SQL,'@Column',@Column)
EXEC(@SQL)

  • :

    • (SQL Management Studio )
    • bigint VARCHAR (50)
    • "EXEC bigIntToGuid" myTable ',' myBigIntColumn '
    • "uniqueidentifier"
    • : newid() /
  • sql script, 1
  • script

int guid .

0

More articles:


All Articles