Bulk modification of unique ids in SQL Server

Question

[This is a bit of an unusual problem, I know...]

What I need is a script that will change every unique id value to new one in our database. The problem is that we have configuration tables that can be exported between instances of our software which is id-sensitive (clobbering existing ids). Years ago, we set up a "wide-enough" id gap between our development "standard configuration" and our client's instances, which is now not wide enough :( - e.g. we're getting id conflicts when clients import our standard configuration.

A sql script to do the following is definitely the simplest/shortest-timeframe thing that we can do. e.g. fixing the code is far too complicated and error prone to consider. Note that we are not "eliminating" the problem here. Just changing the gap from 1000's to 1000000's or more (the existing gap took 5 years to fill).

I believe the simplest solution would be to:

• change all our tables to UPDATE_CASCADE (none of them are - this will greatly simplify the script)
• create an identity table with the new lowest id that we want
• For each table, modify the id to the next one in the identity table (using identity insert modifier flags where necessary). Perhaps after each table is processed, we could reset the identity table.
• turn off UPDATE_CASCADE, and delete the identity table.

Does anybody have any scripts for this (or partial scripts?). I'll upvote anything helpful :).

Answer 1

Why don't you use negative numbers for your standard configuration values and continue to use positive numbers for other things?

Answer 2

Unfortunately UPDATE_CASCADE doesn't exist in the world of Sql Server. I suggest for each table you to re-key you do the following (Pseudo Code)

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

Check it all still works ...

This process could be automated through DMO/SMO objects, but depending on the number of tables involved I'd say using management studio to generate scripts that can then be edited is probably quicker. After all, you only need to do this once/5 years.

Answer 3

Here we go with the code for SQL 2005. It's huge, it's hacky, but it will work (except in the case where you have a primary key that is a composite of two other primary keys).

If someone can re-write this with MrTelly's faster id addition (which wouldn't require building sql from a cursor for each updated row), then I'll mark that as the accepted answer. (If I don't notice the new answer, upvote this - then I'll notice :))

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