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
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.
Categories:
merge replication,
programming,
SQL Server,
T-SQL
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment