Skip to content

Instantly share code, notes, and snippets.

@Sebazzz
Created January 21, 2017 11:26
Show Gist options
  • Save Sebazzz/c1db6f10566160ac656a8be8a81f19ac to your computer and use it in GitHub Desktop.
Save Sebazzz/c1db6f10566160ac656a8be8a81f19ac to your computer and use it in GitHub Desktop.
Scripts to defragment (remove holes) between primary FK keys
-----------------------------------------
-- 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'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment