Scripts to defragment (remove holes) between primary FK keys

defragment-identity-fk.sql

-----------------------------------------
-- Script to "defragment" identity values in tables of the current database
-- 
-- Author: Sebastiaan Dammann
-- 
-- Global overview:
-- This script generates SQL statements you can use to remove gaps in identity
-- values in your SQL database. This script only operates on the dbo schema.
-- You can exclude tables for defragmenting via @tablesToSkip (LIKE query pattern).
--
-- Per table:
--   - Determine new ids to give to records, excluding any collisions
--   - Disable unique key constraints
--   - Copy old records as new record with ID. This is necessary because columns with identity 
--      cannot be updated, and to drop an identity specification the entire table needs to be rebuild.
--   - Disable FK constraints pointing to the current table
--   - Delete old records
--   - Update FK columns of tables
--   - Enable UQ constraints
--   - Reseed identity
--   - Enable FK constraints pointing to the current table
--   - Record statistics

SET NOCOUNT ON

-- Configure the tables to EXCLUDE from defragmenting here
DECLARE @tablesToSkip AS TABLE(TableName NVARCHAR(512) NOT NULL);
INSERT INTO @tablesToSkip (TableName) VALUES 
('MyConstantTable'),
('Localized_%');

-- Declare variables
DECLARE @tableCursor CURSOR;
DECLARE @tableName NVARCHAR(512);
DECLARE @columnName NVARCHAR(512);
DECLARE @tableCols NVARCHAR(MAX)

DECLARE @fkTableCursor CURSOR;
DECLARE @fkTableName NVARCHAR(512);
DECLARE @fkColumnName NVARCHAR(512);
DECLARE @fkName NVARCHAR(512);

DECLARE @ucCursor CURSOR;
DECLARE @ucName NVARCHAR(512);

SET @tableCursor = CURSOR FOR 
    SELECT 
        --s.name as SchemaName,
        t.name as TableName,
        tc.name as ColumnName
        --ic.key_ordinal as KeyOrderNr
    from 
        sys.schemas s 
        inner join sys.tables t   on s.schema_id=t.schema_id
        inner join sys.indexes i  on t.object_id=i.object_id
        inner join sys.index_columns ic on i.object_id=ic.object_id 
										   and i.index_id=ic.index_id
        inner join sys.columns tc on ic.object_id=tc.object_id 
                                 and ic.column_id=tc.column_id
    where i.is_primary_key=1 AND tc.is_identity = 1 AND s.name = 'dbo'
	AND ((SELECT COUNT(*) FROM @tablesToSkip WHERE t.Name LIKE TableName) = 0)
    ORDER BY t.name

PRINT 'BEGIN TRANSACTION rs'
PRINT 'SET NOCOUNT ON'
PRINT 'SET XACT_ABORT OFF'
PRINT 'GO'
PRINT ''
PRINT N'DECLARE @idMap AS TABLE (OldId INT NOT NULL, NewId INT NOT NULL);'
PRINT N'DECLARE @stats AS TABLE (Name NVARCHAR(512) NOT NULL, OldMax INT NOT NULL, NewMax INT NOT NULL, RecordCount INT NOT NULL);'
PRINT N'DECLARE @oldCount INT;'
PRINT N'DECLARE @maxId INT;'

OPEN @tableCursor
FETCH NEXT FROM @tableCursor INTO @tableName, @columnName
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT CONCAT('PRINT ''Processing [', @tableName, ']: [', @columnName, ']'';')

	PRINT CONCAT('     SET @oldCount = (SELECT MAX([', @columnName, ']) FROM [', @tableName, ']);')

    PRINT 'DELETE FROM @idMap'
    PRINT CONCAT('     ALTER TABLE ', @tableName, ' NOCHECK CONSTRAINT ALL;');

	SET @ucCursor = CURSOR FOR 
		SELECT i.name
		FROM sys.indexes i
		JOIN sys.tables t ON t.object_id = i.object_id
		WHERE (i.is_unique_constraint = 1 OR i.is_unique = 1) 
		AND  t.Name = @tableName AND i.is_primary_key = 0

	PRINT '    PRINT ''... Disable UQ constraints'''
	OPEN @ucCursor
    FETCH NEXT FROM @ucCursor INTO @ucName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT CONCAT('    ALTER INDEX  [', @ucName, '] ON [', @tableName, '] DISABLE;')

        FETCH NEXT FROM @ucCursor INTO @ucName
    END
    CLOSE @ucCursor

    PRINT CONCAT('    INSERT INTO @idMap
    SELECT w.ID AS OldId, d.rn AS NewId
    FROM (
        SELECT      d.rn,
                    ROW_NUMBER() OVER (ORDER BY d.rn) AS recID
        FROM        (
                        SELECT  ROW_NUMBER() OVER (ORDER BY [', @columnName,']) AS rn
                        FROM    [', @tableName, ']
                    ) AS d
        LEFT JOIN   dbo.', @tableName, ' AS s ON s.[', @columnName,'] = d.rn
        WHERE       s.[', @columnName,'] IS NULL
    ) AS d
    INNER JOIN  (
        SELECT  [', @columnName,'],
                ROW_NUMBER() OVER (ORDER BY [', @columnName,'] DESC) AS recID
            FROM   [dbo].[', @tableName, ']
    ) AS w ON w.recID = d.recID;')

	PRINT 'SET @maxId = @@ROWCOUNT'
	PRINT 'IF (@maxId > 0)'
	PRINT 'BEGIN'
	PRINT '    PRINT CONCAT(''... Going to process '', @maxId , '' records...'');'

    SET @tableCols = CAST((SELECT CONCAT('[', col.name, '], ')
    FROM sys.columns col
    INNER JOIN sys.tables tbl ON tbl.object_id = col.object_id
    WHERE tbl.name = @tableName AND col.name <> 'Id'
    FOR XML PATH(''),TYPE) AS NVARCHAR(MAX))

	PRINT '    PRINT ''... Insert new record'''
    PRINT CONCAT('    SET IDENTITY_INSERT ', @tableName, ' ON;');
    PRINT CONCAT('    INSERT INTO ', @tableName, ' (', @tableCols,  @columnName, ')')
    PRINT CONCAT('       SELECT ', @tableCols, ' map.NewId AS [', @columnName, '] FROM [', @tableName, '] AS src INNER JOIN @idMap map ON map.OldId = src.[', @columnName, '];')
    PRINT CONCAT('    SET IDENTITY_INSERT ', @tableName, ' OFF;');

    SET @fkTableCursor = CURSOR FOR
        SELECT 
            ccu.table_name AS SourceTable
            ,ccu.constraint_name AS SourceConstraint
            ,ccu.column_name AS SourceColumn
        FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
            INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
                ON ccu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME 
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
                ON kcu.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME  
        WHERE kcu.TABLE_NAME = @tableName AND kcu.COLUMN_NAME = @columnName
        ORDER BY ccu.table_name

	PRINT '    PRINT ''... Disable FK constraints'''
    OPEN @fkTableCursor
    FETCH NEXT FROM @fkTableCursor INTO @fkTableName, @fkName, @fkColumnName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT CONCAT('    ALTER TABLE [', @fkTableName, '] NOCHECK CONSTRAINT [', @fkName, '];')

        FETCH NEXT FROM @fkTableCursor INTO @fkTableName, @fkName, @fkColumnName
    END
    CLOSE @fkTableCursor

	PRINT '    PRINT ''... Delete old records'''
    PRINT CONCAT('    DELETE FROM [', @tableName, '] WHERE [', @columnName, '] IN (SELECT OldId FROM @idMap);' );

    OPEN @fkTableCursor
    FETCH NEXT FROM @fkTableCursor INTO @fkTableName, @fkName, @fkColumnName
    WHILE @@FETCH_STATUS = 0
    BEGIN
		PRINT CONCAT('    UPDATE fkTable SET [', @fkColumnName ,'] = map.NewId FROM ',   @fkTableName, ' fkTable INNER JOIN @idMap map ON map.OldId = fkTable.[', @fkColumnName, '];')
        PRINT CONCAT('    ALTER TABLE [', @fkTableName, '] WITH CHECK CHECK CONSTRAINT [', @fkName, '];')

        FETCH NEXT FROM @fkTableCursor INTO @fkTableName, @fkName, @fkColumnName
    END

    DEALLOCATE @fkTableCursor

	PRINT '    PRINT ''... Enable UQ constraints'''
	OPEN @ucCursor
    FETCH NEXT FROM @ucCursor INTO @ucName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT CONCAT('    ALTER INDEX  [', @ucName, '] ON [', @tableName, '] REBUILD;')

        FETCH NEXT FROM @ucCursor INTO @ucName
    END
    CLOSE @ucCursor
    DEALLOCATE @ucCursor
    
	PRINT '    PRINT ''... Reseed identity'''
	PRINT CONCAT('    SET @maxId = (SELECT MAX([', @columnName, ']) FROM [', @tableName, ']);')
	PRINT CONCAT('    DBCC CHECKIDENT (''', @tableName, ''',RESEED,@maxId);')

	PRINT CONCAT('    INSERT @stats (Name, OldMax, NewMax, RecordCount) SELECT N''', @tableName, ''', @oldCount, (SELECT MAX([', @columnName, ']) FROM [', @tableName, ']), (SELECT COUNT([', @columnName, ']) FROM [', @tableName, ']);')

	PRINT '    PRINT ''... Enable constraints'''
    PRINT CONCAT('    ALTER TABLE [', @tableName, '] CHECK CONSTRAINT ALL');
    --PRINT CONCAT('   DBCC DBREINDEX (''', @tableName, ''', '' '');');
    PRINT '    PRINT ''''';

	PRINT 'END'
    PRINT '';

    FETCH NEXT FROM @tableCursor INTO @tableName, @columnName
END
CLOSE @tableCursor
DEALLOCATE @tableCursor

PRINT 'SELECT *, Diff = OldMax - NewMax FROM @stats ORDER BY (OldMax - NewMax) DESC'
PRINT 'GO'
PRINT 'COMMIT TRANSACTION rs'