Thursday, July 8, 2010

Clean up orphaned identity range constraints

Consider the following scenario: You wanted to move a SQL Server database from one server to another, so you detached it from the original server and attached it at the destination. However, the database was replicated using merge replication and you forgot to delete the database's publications before detaching it. Now, after attaching the database, you find that there are leftover orphaned identity range constraints on the previously replicated tables. I was faced with this problem, so I constructed the following script after some searching on the internet.

DECLARE @constraintname SYSNAME
DECLARE @tablename SYSNAME
DECLARE c1 CURSOR FOR
SELECT SysObjects.[Name] AS [Contraint Name] , Tab.[Name] AS [Table Name]
FROM SysObjects
INNER JOIN (SELECT [Name],[ID] FROM SysObjects WHERE XType = 'U') AS Tab ON Tab.[ID] = Sysobjects.[Parent_Obj]
INNER JOIN sysconstraints ON sysconstraints.Constid = Sysobjects.[ID]
WHERE SysObjects.[Name] LIKE 'repl_identity_range%'
ORDER BY Tab.[Name]

OPEN c1
FETCH NEXT FROM c1 INTO @constraintname, @tablename
WHILE (@@fetch_status <> -1)
BEGIN
EXEC ('ALTER TABLE ' + @tablename + ' DROP CONSTRAINT ' + @constraintname)
FETCH NEXT FROM c1 INTO @constraintname, @tablename
END

DEALLOCATE c1
GO