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

天意易趣网拍卖系统

前台主要功能：首选服务 注销登陆 查看使用帮助 修改添加登陆帐号拍卖商品管理 管理拍卖商品 推荐拍卖商品 删除特定拍卖 已经结束商品 拍卖分类管理 新闻管理 添加文章 删除修改 栏目管理 新闻CSS设定 新闻JS生成 初始化新闻 参数设置 用户管理 未审核用户管理 普通用户管理 高级用户管理 黄金用户管理 管理所有用户 数据库管理 压缩数据库 备份数据库 恢复数据库 批量处理 系统指标测试V1.

0

查看详情

　　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;