ID升GUID升级脚本

数据库整合，为保证id唯一，将id改为guid，由 int 型 Id 改为 UniqueIdentifier 类型。

大致步奏如下：

1. Id删除主键，约束，索引等。
2. Id重命名为OriginalId，作为Id数据备份，关联其余数据表。
3. 新建UniqueIdentifier类型Id。
4. 关联字段重复操作1-3。(ex: OperatorId 重命名 OriginalOperatorId，新建UniqueIdentifier类型OperatorId)
5. 根据OriginalOperatorId与Operator表的OriginalId更新OperatorId。

DECLARE @tableName NVARCHAR(512)
SET @tableName = 't_Billing_AuditLog';--should be updated table

IF OBJECT_ID(@tableName) IS NOT NULL
BEGIN
    DECLARE @columnName NVARCHAR(512)
    SET @columnName = 'Id'; --should be Updated column
    DECLARE @name NVARCHAR(512)

    --delete PK
    DECLARE @constraintName NVARCHAR(128)
    SELECT @constraintName = CONSTRAINT_NAME FROM　INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
    WHERE　TABLE_NAME=@tableName AND CONSTRAINT_NAME like 'PK%'
    IF EXISTs(SELECT * FROM sysobjects WHERE name=@constraintName)
        EXEC('ALTER TABLE ['+ @tableName +'] DROP CONSTRAINT ['+ @constraintName+']');

    --delete other constraint
    SELECT @name = b.name FROM sys.syscolumns a, sys.sysobjects b
    WHERE a.id = OBJECT_ID(@tableName)
    AND b.id = a.cdefault AND a.name = @columnName AND b.name LIKE 'DF%'

    PRINT @name;
    IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@name) AND type='D')
    BEGIN
        EXEC('ALTER TABLE [' + @tableName + '] DROP CONSTRAINT' +@name);
    END

    --delete index
    DECLARE @index NVARCHAR(128)
    SET @index='IX_' --should be update
    IF EXISTS(SELECT 1 FROM sys.indexes WHERE object_id=OBJECT_ID(@tableName,N'U') AND name=@index )
    BEGIN
        EXEC('DROP INDEX '+ @index +' on[' + @tableName + ']');
    END

    --add original column for this cilumn
    IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = OBJECT_ID('@tableName') AND name = 'Original' + @columnName)
    BEGIN
        --rename
        DECLARE @oldName NVARCHAR(512);
        SET @oldName = '[' + @tableName + '].' + @columnName;
        DECLARE @newName NVARCHAR(512);
        SET @newName = 'Original' + @columnName;

        EXEC sp_rename @oldName, @newName;

        --add new column
        ALTER TABLE t_CPanel_Operator ADD [Id] UNIQUEIDENTIFIER NOT NULL DEFAULT(NEWSEQUENTIALID())
        ALTER TABLE t_Cpanel_Operator ADD CONSTRAINT PK_t_Cpanel_Operator PRIMARY KEY NONCLUSTERED
        (
            [Id] ASC
        )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    END

    --other columns
    SET @columnName='Operator';
    SET @name='';

    --delete other constraint
    SELECT @name = b.name FROM sys.syscolumns a, sys.sysobjects b
    WHERE a.id = OBJECT_ID(@tableName)
    AND b.id = a.cdefault AND a.name = @columnName AND b.name LIKE 'DF%'

    PRINT @name;
    IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(@name) AND type='D')
    BEGIN
        EXEC('ALTER TABLE [' + @tableName + '] DROP CONSTRAINT' +@name);
    END

    --add original column for this cilumn
    IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = OBJECT_ID('@tableName') AND name = 'Original' + @columnName)
    BEGIN
        --rename
        SET @oldName = '[' + @tableName + '].' + @columnName;
        SET @newName = 'Original' + @columnName;

        EXEC sp_rename @oldName, @newName;

        --add new column
        ALTER TABLE t_CPanel_Operator ADD [Id] UNIQUEIDENTIFIER NOT NULL DEFAULT(NEWSEQUENTIALID())
        ALTER TABLE t_Cpanel_Operator ADD CONSTRAINT PK_t_Cpanel_Operator PRIMARY KEY NONCLUSTERED
        (
            [Id] ASC
        )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    END

END
GO;
IF OBJECT_ID(@tableName) IS NOT NULL
BEGIN
    IF EXISTS (SELECT * FROM syscolumns WHERE id = OBJECT_ID(@tableName) AND name = 'OriginalOperator')
    BEGIN
        UPDATE t_Billing_AuditLog SET [Operator] = [dbo].[t_Cpanel_Operator].Id --should be update
        FROM [t_Cpanel_Operator] WHERE OriginalOperator = [dbo].[t_Cpanel_Operator].OriginalId --should be update
    END
END

 

转载于:https://www.cnblogs.com/donyblog/p/11088713.html