当前位置:Gxlcms > mysql > (SqlServer)批量清理指定数据库中所有数据

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

时间:2021-07-01 10:21:17 帮助过:29人阅读

欢迎进入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]

人气教程排行