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

　　END CATCH;

　　FETCH NEXT FROM DataBaseTableRelationships

　　INTO @ForeignKey, @ColumnName, @ReferenceTableName, @ReferenceColumnName, @DeleteRule;

　　END;

　　CLOSE DataBaseTableRelationships;

　　DEALLOCATE DataBaseTableRelationships;

　　END

　　PRINT '';

　　PRINT '';

　　FETCH NEXT FROM DataBaseTables2

　　INTO @TableOwner,@TableName;

　　END

　　CLOSE DataBaseTables2;

　　DEALLOCATE DataBaseTables2;