SQL 2000 ; Replication Clearup Script

Written by a colleague, this removes unnecessary constraints and columns when recovering a database previously replicated -

DECLARE @TabName varchar(255)
DECLARE @ConstName varchar(255)
DECLARE @SQL nvarchar(255)
DECLARE @Cnt as int
DECLARE @DBNAME as varchar(255)

SET @DBNAME = ''

use (databasename)

DECLARE Const_cursor CURSOR FOR



SELECT O_Table.[Name],
 O_Const.[Name]

FROM sysConstraints C

INNER JOIN sysObjects O_Table ON O_Table.[ID] = C.[ID]
INNER JOIN sysobjects O_Const ON O_Const.[ID] = C.ConstID

WHERE O_Const.[Name] LIKE 'DF__' + LEFT(O_Table.Name,9) + '__msrep__%'

ORDER BY 1,2

SET @Cnt = 0

OPEN Const_cursor

FETCH NEXT FROM Const_cursor INTO @TabName, @ConstName

WHILE @@FETCH_STATUS = 0
  BEGIN
PRINT 'Processing ' + @TabName
SET @Cnt = @Cnt + 1

--First we need to drop the constraint
SELECT @SQL = 'ALTER TABLE ' + @DBNAME + @TabName + ' DROP CONSTRAINT ' + @ConstName
exec sp_executesql @SQL
PRINT @SQL

--Now drop the unneeded column
SELECT @SQL = 'ALTER TABLE ' + @DBNAME + @TabName + ' DROP COLUMN msrepl_tran_version'
exec sp_executesql @SQL
PRINT @SQL

FETCH NEXT FROM Const_cursor INTO @TabName, @ConstName
  END

PRINT ''
PRINT cast(@Cnt as varchar(3)) + ' Tables Processed'

CLOSE Const_cursor
DEALLOCATE Const_cursor

GO