|
1029usr078198
Forum Admin
USA
333 Posts |
 Posted - December 31 2007 : 13:34:49
|
When you turn off replication in SQL Server, often tables, views, triggers, and settings are left that are used to manage replication conflicts. This script can be run in the SQL Query Analyzer if you want to remove the views and conflict tables. If, after a not-thoroughly-successful disabling of replication, you re-enable replication, the conflict tables will be named beginning with aonflict_ rather than conflict_ and you may have to modify the script below accordingly. I found the greatest portion of this script on SQL Server Central at http://www.sqlservercentral.com/Forums/Topic366417-7-1.aspx. Registration is required to view that post.
-- Script to drop all conflict tables and views
-- in the database
-- This script MUST be run when there is no activity in the database
-- Use this script ONLY to clean up replication that's already been
-- turned off.
-- CLV 2/21/2007, Mod by DAConsult 12/31/2007
-- Enter the name of the database with the conflict tables
USE databasename
-- Declare Variables
SET NOCOUNT ON
DECLARE @TableName varchar(255)
declare @sTableName nvarchar(100)
declare @smsg as Varchar(100)
declare @Type as varchar(1)
-- Create a table to hold names of the tables to drop
DECLARE TableCursor CURSOR FOR
SELECT name, type FROM sysobjects
WHERE (type = 'V' AND name LIKE '%tsvw_%')
OR (type = 'V' AND name LIKE '%ctsv_%')
OR (type = 'U' AND name like '%conflict_%')
OPEN TableCursor
-- Fetch the table names one by one and cycle through dropping them
FETCH NEXT FROM TableCursor INTO @TableName, @Type
WHILE @@FETCH_STATUS = 0
BEGIN
-- Load the conflict table names one by one
-- create and execute a query on the fly
-- to drop each of the conflict tables
set @sTableName=@TableName
IF @Type = 'V'
set @smsg='drop view' + ' ' + @sTableName
ELSE
set @smsg='drop table' + ' ' + @sTableName
exec(@smsg)
FETCH NEXT FROM TableCursor INTO @TableName, @Type
END
-- Close and deallocate the cursor
CLOSE TableCursor
DEALLOCATE TableCursor
Hope it helps,
David |
|
Cleaning up after replication is disabled
