萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mssql數據庫 >> SqlServer批量清理指定數據庫中所有數據

SqlServer批量清理指定數據庫中所有數據

   在實際應用中,當我們准備把一個項目移交至客戶手中使用時,我們需要把庫中所有表先前的測試數據清空,以給客戶一個干淨的數據庫,如果涉及的表很多,要一一的清空,不僅花費時間,還容易出錯以及漏刪,在這兒我提供了一個方法,可快捷有效的清空指定數據庫所有表的數據。僅供參考,歡迎交流不同意見。

  --Remove all data from a database

  SET NOCOUNT ON

  --Tables to ignore

  DECLARE @IgnoreTables

  TABLE (TableName varchar(512))

  INSERT INTO @IgnoreTables (TableName) VALUES ('sysdiagrams')

  DECLARE @AllRelationships

  TABLE (ForeignKey varchar(512)

  ,TableName varchar(512)

  ,ColumnName varchar(512)

  ,ReferenceTableName varchar(512)

  ,ReferenceColumnName varchar(512)

  ,DeleteRule varchar(512))

  INSERT INTO @AllRelationships

  SELECT f.name AS ForeignKey,

  OBJECT_NAME(f.parent_object_id) AS TableName,

  COL_NAME(fc.parent_object_id,

  fc.parent_column_id) AS ColumnName,

  OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,

  COL_NAME(fc.referenced_object_id,

  fc.referenced_column_id) AS ReferenceColumnName,

  delete_referential_action_desc as DeleteRule

  FROM sys.foreign_keys AS f

  INNER JOIN sys.foreign_key_columns AS fc

  ON f.OBJECT_ID = fc.constraint_object_id

  DECLARE @TableOwner varchar(512)

  DECLARE @TableName varchar(512)

  DECLARE @ForeignKey varchar(512)

  DECLARE @ColumnName varchar(512)

  DECLARE @ReferenceTableName varchar(512)

  DECLARE @ReferenceColumnName varchar(512)

  DECLARE @DeleteRule varchar(512)

  PRINT('Loop through all tables and switch all constraints to have a delete rule of CASCADE')

  DECLARE DataBaseTables0

  CURSOR FOR

  SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name

  FROM sys.tables AS t;

  OPEN DataBaseTables0;

  FETCH NEXT FROM DataBaseTables0

  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

  FROM @AllRelationships

  WHERE TableName = @TableName

  OPEN DataBaseTableRelationships;

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

  IF @@FETCH_STATUS <> 0

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

  WHILE @@FETCH_STATUS = 0

  BEGIN

  PRINT '=====> switching delete rule on ' + @ForeignKey + ' to CASCADE';

  BEGIN TRANSACTION

  BEGIN TRY

  EXEC('

  ALTER TABLE ['+@TableOwner+'].[' + @TableName + ']

  DROP CONSTRAINT '+@ForeignKey+';

  ALTER TABLE ['+@TableOwner+'].[' + @TableName + '] ADD CONSTRAINT

  '+@ForeignKey+' FOREIGN KEY

  (

  '+@ColumnName+'

  ) REFERENCES '+@ReferenceTableName+'

  (

  '+@ReferenceColumnName+'

  ) ON DELETE CASCADE;

  ');

  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

  PRINT '=====> switching delete rule on ' + @ForeignKey + ' to ' + @switchBackTo;

  BEGIN TRANSACTION

  BEGIN TRY

  EXEC('

  ALTER TABLE ['+@TableOwner+'].[' + @TableName + ']

  DROP CONSTRAINT '+@ForeignKey+';

  ALTER TABLE ['+@TableOwner+'].[' + @TableName + '] ADD CONSTRAINT

  '+@ForeignKey+' FOREIGN KEY

  (

  '+@ColumnName+'

  ) REFERENCES '+@ReferenceTableName+'

  (

  '+@ReferenceColumnName+'

  ) ON DELETE '+@switchBackTo+'

  ');

  COMMIT TRANSACTION

  END TRY

  BEGIN CATCH

  PRINT '=====> can''t change '+@ForeignKey + ' back to '+ @switchBackTo +', - ' +

  CAST(ERROR_NUMBER() AS VARCHAR) + ' - ' + ERROR_MESSAGE();

  ROLLBACK TRANSACTION

  E

copyright © 萬盛學電腦網 all rights reserved