本文主要是介绍增量数据库同步软件PanguSync侵入式全面清理脚本，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
Sqlserver
-- 变量声明
DECLARE @tableName NVARCHAR(256)
DECLARE @indexName NVARCHAR(128)
DECLARE @triggerName NVARCHAR(256)
DECLARE @sql NVARCHAR(MAX)
DECLARE @constraintsname NVARCHAR(256)
-- 声明游标来遍历所有用户表
DECLARE curTables CURSOR FOR
SELECT name
FROM sys.tables
WHERE type = 'U' -- 用户表 -- 打开游标
OPEN curTables
FETCH NEXT FROM curTables INTO @tableName -- 循环遍历所有表
WHILE @@FETCH_STATUS = 0
BEGIN -- 删除索引source BEGIN TRY SELECT @indexName = name FROM sys.indexes WHERE object_id = OBJECT_ID(@tableName) AND name = 'I_PanguSyncSourceTimestamp' IF @indexName IS NOT NULL BEGIN SET @sql = 'DROP INDEX ' + QUOTENAME(@indexName) + ' ON ' + QUOTENAME(@tableName) + ';' EXEC sp_executesql @sql PRINT 'Index I_PanguSyncSourceTimestamp dropped from ' + @tableName + '.' END END TRY BEGIN CATCH PRINT 'Error dropping index I_PanguSyncSourceTimestamp from ' + @tableName + '. Error: ' + ERROR_MESSAGE() END CATCH --删除CONSTRAINT sourceSELECT top 1 @constraintsname= c.name FROM sysconstraints a
INNER JOIN syscolumns b on a.colid=b.colid
INNER JOIN sysobjects c on a.constid=c.id
WHERE a.id=object_id(@tableName)
AND b.name='C_PanguSyncSourceTimestamp'IF @constraintsname IS NOT NULL BEGIN SET @sql = 'ALTER TABLE ' + QUOTENAME(@tableName) + ' DROP CONSTRAINT '+ QUOTENAME(@constraintsname)+';'EXEC sp_executesql @sql END -- 删除字段 sourceIF EXISTS ( SELECT 1 FROM sys.columns WHERE name = 'C_PanguSyncSourceTimestamp' AND object_id = OBJECT_ID(@tableName) ) BEGIN SET @sql = 'ALTER TABLE ' + QUOTENAME(@tableName) + ' DROP COLUMN C_PanguSyncSourceTimestamp;' EXEC sp_executesql @sql PRINT 'Column C_PanguSyncSourceTimestamp dropped from ' + @tableName END -- 删除索引 targetBEGIN TRY SELECT @indexName = name FROM sys.indexes WHERE object_id = OBJECT_ID(@tableName) AND name = 'I_PanguSyncTargetTimestamp' IF @indexName IS NOT NULL BEGIN SET @sql = 'DROP INDEX ' + QUOTENAME(@indexName) + ' ON ' + QUOTENAME(@tableName) + ';' EXEC sp_executesql @sql PRINT 'Index I_PanguSyncTargetTimestamp dropped from ' + @tableName + '.' END END TRY BEGIN CATCH PRINT 'Error dropping index I_PanguSyncTargetTimestamp from ' + @tableName + '. Error: ' + ERROR_MESSAGE() END CATCH --删除CONSTRAINT targetSELECT top 1 @constraintsname= c.name FROM sysconstraints a
INNER JOIN syscolumns b on a.colid=b.colid
INNER JOIN sysobjects c on a.constid=c.id
WHERE a.id=object_id(@tableName)
AND b.name='C_PanguSyncTargetTimestamp'IF @constraintsname IS NOT NULL BEGIN SET @sql = 'ALTER TABLE ' + QUOTENAME(@tableName) + ' DROP CONSTRAINT '+ QUOTENAME(@constraintsname)+';'EXEC sp_executesql @sql END -- 删除字段 targetIF EXISTS ( SELECT 1 FROM sys.columns WHERE name = 'C_PanguSyncTargetTimestamp' AND object_id = OBJECT_ID(@tableName) ) BEGIN SET @sql = 'ALTER TABLE ' + QUOTENAME(@tableName) + ' DROP COLUMN C_PanguSyncTargetTimestamp;' EXEC sp_executesql @sql PRINT 'Column C_PanguSyncTargetTimestamp dropped from ' + @tableName END -- 删除触发器（包含PGD或PanguSync） DECLARE curTriggers CURSOR FOR SELECT name FROM sys.triggers WHERE OBJECT_NAME(parent_id) = @tableName AND name LIKE '%_PGD_%' OR name LIKE '%PanguSync%' OPEN curTriggers FETCH NEXT FROM curTriggers INTO @triggerName WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'DROP TRIGGER ' + QUOTENAME(@triggerName) + ';' EXEC sp_executesql @sql PRINT 'Trigger ' + @triggerName + ' dropped from ' + @tableName + '.' FETCH NEXT FROM curTriggers INTO @triggerName END CLOSE curTriggers DEALLOCATE curTriggers -- 获取下一个表名 FETCH NEXT FROM curTables INTO @tableName
END -- 关闭并释放游标
CLOSE curTables
DEALLOCATE curTables这篇关于增量数据库同步软件PanguSync侵入式全面清理脚本的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
