[推荐] (SqlServer)批量清理指定数据库中所有数据

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
函数计算FC,每月15万CU 3个月
简介:
-- 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;  

 


本文转自yonghu86 51CTO博客,原文链接:http://blog.51cto.com/yonghu/1321333,如需转载请自行联系原作者

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
相关文章
|
7天前
|
数据采集 数据库 Python
有哪些方法可以验证用户输入数据的格式是否符合数据库的要求?
有哪些方法可以验证用户输入数据的格式是否符合数据库的要求?
115 75
|
11天前
|
数据库 Windows
SqlServer数据恢复—SqlServer数据库所在分区损坏的数据恢复案例
一块硬盘上存放的SqlServer数据库,windows server操作系统+NTFS文件系统。由于误操作导致分区损坏,需要恢复硬盘里的SqlServer数据库数据。
|
20天前
|
SQL 存储 运维
从建模到运维:联犀如何完美融入时序数据库 TDengine 实现物联网数据流畅管理
本篇文章是“2024,我想和 TDengine 谈谈”征文活动的三等奖作品。文章从一个具体的业务场景出发,分析了企业在面对海量时序数据时的挑战,并提出了利用 TDengine 高效处理和存储数据的方法,帮助企业解决在数据采集、存储、分析等方面的痛点。通过这篇文章,作者不仅展示了自己对数据处理技术的理解,还进一步阐释了时序数据库在行业中的潜力与应用价值,为读者提供了很多实际的操作思路和技术选型的参考。
32 1
|
24天前
|
存储 Java easyexcel
招行面试:100万级别数据的Excel,如何秒级导入到数据库?
本文由40岁老架构师尼恩撰写,分享了应对招商银行Java后端面试绝命12题的经验。文章详细介绍了如何通过系统化准备,在面试中展示强大的技术实力。针对百万级数据的Excel导入难题,尼恩推荐使用阿里巴巴开源的EasyExcel框架,并结合高性能分片读取、Disruptor队列缓冲和高并发批量写入的架构方案,实现高效的数据处理。此外,文章还提供了完整的代码示例和配置说明,帮助读者快速掌握相关技能。建议读者参考《尼恩Java面试宝典PDF》进行系统化刷题,提升面试竞争力。关注公众号【技术自由圈】可获取更多技术资源和指导。
|
27天前
|
前端开发 JavaScript 数据库
获取数据库中字段的数据作为下拉框选项
获取数据库中字段的数据作为下拉框选项
54 5
|
2月前
|
关系型数据库 MySQL 数据库
GBase 数据库如何像MYSQL一样存放多行数据
GBase 数据库如何像MYSQL一样存放多行数据
|
1天前
|
缓存 关系型数据库 MySQL
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧,涵盖基础优化、高级技巧及性能监控。
11 0
|
28天前
|
存储 Oracle 关系型数据库
数据库传奇:MySQL创世之父的两千金My、Maria
《数据库传奇:MySQL创世之父的两千金My、Maria》介绍了MySQL的发展历程及其分支MariaDB。MySQL由Michael Widenius等人于1994年创建,现归Oracle所有,广泛应用于阿里巴巴、腾讯等企业。2009年,Widenius因担心Oracle收购影响MySQL的开源性,创建了MariaDB,提供额外功能和改进。维基百科、Google等已逐步替换为MariaDB,以确保更好的性能和社区支持。掌握MariaDB作为备用方案,对未来发展至关重要。
58 3
|
28天前
|
安全 关系型数据库 MySQL
MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!
《MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!》介绍了MySQL中的三种关键日志:二进制日志(Binary Log)、重做日志(Redo Log)和撤销日志(Undo Log)。这些日志确保了数据库的ACID特性,即原子性、一致性、隔离性和持久性。Redo Log记录数据页的物理修改,保证事务持久性;Undo Log记录事务的逆操作,支持回滚和多版本并发控制(MVCC)。文章还详细对比了InnoDB和MyISAM存储引擎在事务支持、锁定机制、并发性等方面的差异,强调了InnoDB在高并发和事务处理中的优势。通过这些机制,MySQL能够在事务执行、崩溃和恢复过程中保持
70 3
|
28天前
|
SQL 关系型数据库 MySQL
数据库灾难应对:MySQL误删除数据的救赎之道,技巧get起来!之binlog
《数据库灾难应对:MySQL误删除数据的救赎之道,技巧get起来!之binlog》介绍了如何利用MySQL的二进制日志(Binlog)恢复误删除的数据。主要内容包括: 1. **启用二进制日志**:在`my.cnf`中配置`log-bin`并重启MySQL服务。 2. **查看二进制日志文件**:使用`SHOW VARIABLES LIKE &#39;log_%&#39;;`和`SHOW MASTER STATUS;`命令获取当前日志文件及位置。 3. **创建数据备份**:确保在恢复前已有备份,以防意外。 4. **导出二进制日志为SQL语句**:使用`mysqlbinlog`
87 2

热门文章

最新文章