(SqlServer)批量清理指定数据库中所有数据

欢迎进入Windows社区论坛，与300万技术人员互动交流 >>进入 COMMIT TRANSACTION END TRY BEGIN CATCH PRINT '===== can''t switch ' + @ForeignKey + ' to CASCADE, - ' + CAST（ERROR_NUMBER（） AS VARCHAR） + ' - ' + ERROR_MESSAGE（）； ROLLBACK TRAN

欢迎进入Windows社区论坛，与300万技术人员互动交流 >>进入

　　COMMIT TRANSACTION

　　END TRY

　　BEGIN CATCH

　　PRINT '=====> can''t switch ' + @ForeignKey + ' to CASCADE, - ' +

　　CAST（ERROR_NUMBER（） AS VARCHAR） + ' - ' + ERROR_MESSAGE（）；

　　ROLLBACK TRANSACTION

　　END CATCH;

　　FETCH NEXT FROM DataBaseTableRelationships INTO @ForeignKey, @ColumnName, @ReferenceTableName, @ReferenceColumnName;

　　END;

　　CLOSE DataBaseTableRelationships;

　　DEALLOCATE DataBaseTableRelationships;

　　END

　　PRINT '';

　　PRINT '';

　　FETCH NEXT FROM DataBaseTables0

　　INTO @TableOwner,@TableName;

　　END

　　CLOSE DataBaseTables0;

　　DEALLOCATE DataBaseTables0;

　　PRINT（'Loop though each table and DELETE All data from the table'）

　　DECLARE DataBaseTables1 CURSOR FOR

　　SELECT SCHEMA_NAME（t.schema_id） AS schema_name, t.name AS table_name

　　FROM sys.tables AS t;

　　OPEN DataBaseTables1;

　　FETCH NEXT FROM DataBaseTables1

　　INTO @TableOwner,@TableName;

　　WHILE @@FETCH_STATUS = 0

　　BEGIN

　　IF （NOT EXISTS（SELECT TOP 1 1 FROM @IgnoreTables WHERE TableName = @TableName））

　　BEGIN

　　PRINT '['+@TableOwner+'].[' + @TableName + ']';

　　PRINT '=====> deleting data from ['+@TableOwner+'].[' + @TableName + ']';

　　BEGIN TRY

　　EXEC（'

　　DELETE FROM ['+@TableOwner+'].[' + @TableName + ']

　　DBCC CHECKIDENT （[' + @TableName + '], RESEED, 0）

　　'）；

　　END TRY

　　BEGIN CATCH

　　PRINT '=====> can''t FROM ['+@TableOwner+'].[' + @TableName + '], - ' +

　　CAST（ERROR_NUMBER（） AS VARCHAR） + ' - ' + ERROR_MESSAGE（）；

　　END CATCH;

　　END

　　PRINT '';

　　PRINT '';

　　FETCH NEXT FROM DataBaseTables1

　　INTO @TableOwner,@TableName;

　　END

　　CLOSE DataBaseTables1;

　　DEALLOCATE DataBaseTables1;

　　PRINT（'Loop through all tables and switch all constraints to have a delete rule they had at the beggining of the task'）

　　DECLARE DataBaseTables2 CURSOR FOR

　　SELECT SCHEMA_NAME（t.schema_id） AS schema_name, t.name AS table_name

　　FROM sys.tables AS t;

　　OPEN DataBaseTables2;

　　FETCH NEXT FROM DataBaseTables2

　　INTO @TableOwner,@TableName;

　　WHILE @@FETCH_STATUS = 0

　　BEGIN

　　IF （NOT EXISTS（SELECT TOP 1 1 FROM @IgnoreTables WHERE TableName = @TableName））

　　BEGIN

　　PRINT '['+@TableOwner+'].[' + @TableName + ']';

　　DECLARE DataBaseTableRelationships CURSOR FOR

　　SELECT ForeignKey, ColumnName, ReferenceTableName, ReferenceColumnName, DeleteRule

　　FROM @AllRelationships

　　WHERE TableName = @TableName

　　OPEN DataBaseTableRelationships;

　　FETCH NEXT FROM DataBaseTableRelationships INTO @ForeignKey, @ColumnName, @ReferenceTableName, @ReferenceColumnName, @DeleteRule;

　　IF @@FETCH_STATUS 0

　　PRINT '=====> No Relationships' ;

　　WHILE @@FETCH_STATUS = 0

　　BEGIN

　　DECLARE @switchBackTo varchar（50） =

　　CASE

　　WHEN @DeleteRule = 'NO_ACTION' THEN 'NO ACTION'

　　WHEN @DeleteRule = 'CASCADE' THEN 'CASCADE'

　　WHEN @DeleteRule = 'SET_NULL' THEN 'SET NULL'

　　WHEN @DeleteRule = 'SET_DEFAULT' THEN 'SET DEFAULT'

　　END

[1] [2] [3]