-- 变量声明
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
BEGIN TRY
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
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
BEGIN TRY
-- 删除字段 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
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
-- 删除索引 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
BEGIN TRY
--删除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
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
-- 删除字段 target
BEGIN TRY
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
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
-- 删除触发器(包含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