批量修改SQL Server中的唯一ID

Question

[这是一个有点不寻常的问题，我知道...]

我需要一个脚本，它将把我们数据库中的每个唯一的 id 值更改为新的值。 问题是我们的配置表可以在 id 敏感的软件实例之间导出（破坏现有的 id）。 几年前，我们在开发“标准配置”和客户实例之间设置了“足够宽”的 id 差距，但现在已经不够宽了:( - 例如，当我们遇到 id 冲突时客户端导入我们的标准配置

绝对是我们可以做的最简单/最短时间的事情，例如修复代码太复杂并且容易出错，请注意，我们并没有“消除”。这里的问题只是将差距从 1000 更改为 1000000 或更多（现有的差距需要 5 年才能填补

）

• 。脚本）
• 创建一个具有我们想要的新的最低 id 的标识表
• 对于每个表，将 id 修改为标识表中的下一个标识（必要时使用标识插入修饰符标志）也许在处理每个表后，我们可以重置标识表。 。
• 关闭UPDATE_CASCADE，并删除身份表

我正在为此寻找任何（部分或完整）脚本。

Answer 1

不幸的是，UPDATE_CASCADE 在 Sql Server 的世界中不存在。 我建议您为每个要重新键入的表执行以下操作（伪代码）

BACKUP DATABASE
CHECK BACKUP WORKS!

FOR EACH TABLE TO BE RE-KEYED
   DROP ALL FOREIGN KEY CONSTRAINTS, INDEXES ETC FROM TABLE

   SELECT ID + Number, ALL_OTHER_FIELDS INTO TEMP_TABLE FROM TABLE
   RENAME TABLE OLD_TABLE
   RENAME TEMP_TABLE TABLE

   FOR ALL TABLES REFERENCING THIS TABLE
       UPDATE FOREIGN_KEY_TABLE SET FK_ID = FK_ID + new number
   END FOR

   RE-APPLY FOREIGN KEY CONSTRAINTS, INDEXES ETC FROM TABLE

END FOR

检查一切是否仍然有效...

此过程可以通过 DMO/SMO 对象自动执行，但根据涉及的表数量，我会说使用Management Studio 生成可以编辑的脚本可能会更快。 毕竟，您只需每 5 年执行一次即可。

Answer 2

这里我们使用 SQL 2005 的代码。它很大，很hacky，但是它会工作（除非你有一个由其他两个主键组合而成的主键）。

如果有人可以用 MrTelly 更快的 id 添加（这不需要从游标为每个更新的行构建 sql）来重写它，那么我会将其标记为已接受的答案。 （如果我没有注意到新答案，请投票 - 然后我会注意到:)）

BEGIN TRAN
SET NOCOUNT ON;

DECLARE @newLowId INT
SET @newLowId = 1000000

DECLARE @sql VARCHAR(4000)

--**** SELECT ALL TABLES WITH IDENTITY COLUMNS ****
DECLARE tables  SCROLL CURSOR
FOR 
SELECT '[' + SCHEMA_NAME(schema_id) + '].[' + t.name + ']', c.name
FROM sys.identity_columns c
INNER JOIN sys.objects t
    on c.object_id = t.object_id
WHERE t.type_Desc = 'USER_TABLE'

OPEN tables

DECLARE @Table VARCHAR(100)
DECLARE @IdColumn VARCHAR(100)

CREATE Table #IdTable(
  id INT IDENTITY(1,1),
  s CHAR(1)
)

FETCH FIRST FROM tables
INTO @Table, @IdColumn

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT('
****************** '+@Table+' ******************
')
    --Reset the idtable to the 'low' id mark - remove this line if you want all records to have distinct ids across the database
    DELETE FROM #IdTable
    DBCC CHECKIDENT('#IdTable', RESEED, @newLowId)

    --**** GENERATE COLUMN SQL (for inserts and deletes - updating identities is not allowed) ****
    DECLARE tableColumns CURSOR FOR
        SELECT column_name FROM information_schema.columns
        WHERE '[' + table_schema + '].[' + table_name + ']' = @Table
        AND column_name <> @IdColumn
    OPEN tableColumns
    DECLARE @columnName VARCHAR(100)
    DECLARE @columns VARCHAR(4000)
    SET @columns = ''
    FETCH NEXT FROM tableColumns INTO @columnName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @columns = @columns + @columnName
        FETCH NEXT FROM tableColumns INTO @columnName
        IF @@FETCH_STATUS = 0 SET @columns = @columns + ', '
    END

    CLOSE tableColumns
    DEALLOCATE tableColumns

    --**** GENERATE FOREIGN ROW UPDATE SQL ****
    DECLARE foreignkeys SCROLL CURSOR
    FOR 
    SELECT con.name, 
        '[' + SCHEMA_NAME(f.schema_id) + '].[' + f.name + ']' fTable, fc.column_name , 
        '[' + SCHEMA_NAME(p.schema_id) + '].[' + p.name + ']' pTable,  pc.column_name 
    FROM sys.foreign_keys con
    INNER JOIN sysforeignkeys syscon
        ON con.object_id = syscon.constid
    INNER JOIN sys.objects f
        ON con.parent_object_id = f.object_id
    INNER JOIN information_schema.columns fc
        ON fc.table_schema = SCHEMA_NAME(f.schema_id)
        AND fc.table_name = f.name
        AND fc.ordinal_position = syscon.fkey

    INNER JOIN sys.objects p
        ON con.referenced_object_id = p.object_id
    INNER JOIN information_schema.columns pc
        ON pc.table_schema = SCHEMA_NAME(p.schema_id)
        AND pc.table_name = p.name
        AND pc.ordinal_position = syscon.rkey
    WHERE '[' + SCHEMA_NAME(p.schema_id) + '].[' + p.name + ']' = @Table

    OPEN foreignkeys

    DECLARE @FKeyName VARCHAR(100)
    DECLARE @FTable VARCHAR(100)
    DECLARE @FColumn VARCHAR(100)
    DECLARE @PTable VARCHAR(100)
    DECLARE @PColumn VARCHAR(100)

    --**** RE-WRITE ALL IDS IN THE TABLE ****
    SET @sql='DECLARE tablerows CURSOR FOR
    SELECT CAST('+@IdColumn+' AS VARCHAR) FROM '+@Table+' ORDER BY '+@IdColumn
    PRINT(@sql)
    exec(@sql)

    OPEN tablerows
    DECLARE @rowid VARCHAR(100)
    DECLARE @id VARCHAR(100)


    FETCH NEXT FROM tablerows INTO @rowid
    WHILE @@FETCH_STATUS = 0
    BEGIN
        --generate new id
        INSERT INTO #IdTable VALUES ('')
        SELECT @id = CAST(@@IDENTITY AS VARCHAR)
        IF @rowId <> @Id
        BEGIN
            PRINT('Modifying '+@Table+': changing '+@rowId+' to '+@id)
            SET @sql='SET IDENTITY_INSERT ' + @Table + ' ON
    INSERT INTO '+@Table+' ('+@IdColumn+','+@columns+') SELECT '+@id+','+@columns+' FROM '+@Table+' WHERE '+@IdColumn+'='+@rowId

            --Updating all foreign rows...
            FETCH FIRST FROM foreignkeys
            INTO @FKeyName, @FTable, @FColumn, @PTable, @PColumn

            WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @sql = @sql + '
    UPDATE '+@FTable+' SET '+@FColumn+'='+@id+' WHERE '+@FColumn+' ='+@rowId
                FETCH NEXT FROM foreignkeys
                INTO @FKeyName, @FTable, @FColumn, @PTable, @PColumn
            END
            SET @sql=@sql + '
    DELETE FROM '+@Table+' WHERE '+@IdColumn+'='+@rowId

            PRINT(@sql)
            exec(@sql)
        END
        FETCH NEXT FROM tablerows INTO @rowid
    END

    CLOSE tablerows
    DEALLOCATE tablerows
    CLOSE foreignkeys
    DEALLOCATE foreignkeys

    --Revert to normal identity operation - update the identity to the latest id...
    DBCC CHECKIDENT(@Table, RESEED, @@IDENTITY)
    SET @sql='SET IDENTITY_INSERT ' + @Table + ' OFF'
    PRINT(@sql)
    exec(@sql)

    FETCH NEXT FROM tables
    INTO @Table, @IdColumn
END

CLOSE tables
DEALLOCATE tables

DROP TABLE #IdTable
--COMMIT
--ROLLBACK

Answer 3

为什么不使用负数作为标准配置值并继续使用正数作为其他值？