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 source
	  SELECT 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  
		
		
	
    -- 删除字段  source
    IF 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  
		
		
		
		 -- 删除索引 target
    BEGIN 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 target
	  SELECT 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  
		
		
	
    -- 删除字段  target
    IF 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