(SqlServer)批量清理指定数据库中所有数据
来源:互联网 发布:java aes文件加密解密 编辑:程序博客网 时间:2024/06/05 17:26
在实际应用中,当我们准备把一个项目移交至客户手中使用时,我们需要把库中所有表先前的测试数据清空,以给客户一个干净的数据库,如果涉及的表很多,要一一的清空,不仅花费时间,还容易出错以及漏删,在这儿我提供了一个方法,可快捷有效的清空指定数据库所有表的数据。仅供参考,欢迎交流不同意见。
--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_columnsAS fc
ON f.OBJECT_ID= fc.constraint_object_id
DECLARE @TableOwnervarchar(512)
DECLARE @TableNamevarchar(512)
DECLARE @ForeignKeyvarchar(512)
DECLARE @ColumnNamevarchar(512)
DECLARE @ReferenceTableNamevarchar(512)
DECLARE @ReferenceColumnNamevarchar(512)
DECLARE @DeleteRulevarchar(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 TOP1 1 FROM @IgnoreTablesWHERE TableName =@TableName))
BEGIN
PRINT '['+@TableOwner+'].['+ @TableName + ']';
DECLARE DataBaseTableRelationships CURSOR FOR
SELECT ForeignKey, ColumnName, ReferenceTableName, ReferenceColumnName
FROM @AllRelationships
WHERE TableName = @TableName
OPEN DataBaseTableRelationships;
FETCH NEXTFROM 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 NEXTFROM DataBaseTableRelationships INTO @ForeignKey, @ColumnName, @ReferenceTableName, @ReferenceColumnName;
END;
CLOSE DataBaseTableRelationships;
DEALLOCATE DataBaseTableRelationships;
END
PRINT '';
PRINT '';
FETCH NEXTFROM 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 TOP1 1 FROM @IgnoreTablesWHERE 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 TOP1 1 FROM @IgnoreTablesWHERE 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 @switchBackTovarchar(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 NEXTFROM 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;
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_columnsAS fc
ON f.OBJECT_ID= fc.constraint_object_id
DECLARE @TableOwnervarchar(512)
DECLARE @TableNamevarchar(512)
DECLARE @ForeignKeyvarchar(512)
DECLARE @ColumnNamevarchar(512)
DECLARE @ReferenceTableNamevarchar(512)
DECLARE @ReferenceColumnNamevarchar(512)
DECLARE @DeleteRulevarchar(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 TOP1 1 FROM @IgnoreTablesWHERE TableName =@TableName))
BEGIN
PRINT '['+@TableOwner+'].['+ @TableName + ']';
DECLARE DataBaseTableRelationships CURSOR FOR
SELECT ForeignKey, ColumnName, ReferenceTableName, ReferenceColumnName
FROM @AllRelationships
WHERE TableName = @TableName
OPEN DataBaseTableRelationships;
FETCH NEXTFROM 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 NEXTFROM DataBaseTableRelationships INTO @ForeignKey, @ColumnName, @ReferenceTableName, @ReferenceColumnName;
END;
CLOSE DataBaseTableRelationships;
DEALLOCATE DataBaseTableRelationships;
END
PRINT '';
PRINT '';
FETCH NEXTFROM 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 TOP1 1 FROM @IgnoreTablesWHERE 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 TOP1 1 FROM @IgnoreTablesWHERE 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 @switchBackTovarchar(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 NEXTFROM 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;
- [推荐] (SqlServer)批量清理指定数据库中所有数据
- (SqlServer)批量清理指定数据库中所有数据
- (SqlServer)批量清理指定数据库中所有数据
- SqlServer批量清理指定数据库中所有数据
- [推荐] (SqlServer)批量清理指定数据库中所有数据
- SqlServer批量清理指定数据库中所有数据
- [MSSQL]批量清理指定数据库中所有数据
- Sqlserver数据库批量导入数据
- C#批量附加指定目录下的所有数据库文件到数据库中
- C# 批量插入表SQLSERVER SqlBulkCopy往数据库中批量插入数据
- sqlserver数据库日志清理
- SqlServer清理数据库日志
- 清理SQLServer数据库日志
- 清理数据库中对应某个字段的所有非法数据的一种解决方案 SQL语句
- SqlServer查找指定数据库数据库下的所有表名
- 【SQLSERVER】清空数据库所有表数据
- mysql 批量删除数据库中的所有数据
- 批量高效向sqlserver中插入数据
- 由12306.cn谈谈网站性能技术
- org.apache.commons.beanutils.PropertyUtils使用明细
- 如何解决warning: incompatible implicit declaration of built-in function 'malloc'
- ASP.NET的AJAX调用Web Service方法和后台页面的方法
- 360极速版+刷火车票应用----Chrome内核刷票 + IE内核网银付款,完美解决Chrome内核网银支付问题
- (SqlServer)批量清理指定数据库中所有数据
- Beatles小记(三)-分布式数据流分析中Master的横向扩展
- 在IE6、ie7下input选择框与文字对齐 和ie更高版本无法一致的解决办法
- 一个通过JSONP跨域调用WCF REST服务的例子(以jQuery为例)
- mysql5.5 for linux 安装
- 常用汇编指令(备忘)
- iterator_traits和__type_traits
- 主程序调用DLL窗体cannot assign a class to a class问题的彻底解决方案
- 在Dll中调用自身的位图资源