[推荐] (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
相关文章
|
1月前
|
存储 人工智能 Cloud Native
云栖重磅|从数据到智能:Data+AI驱动的云原生数据库
在9月20日2024云栖大会上,阿里云智能集团副总裁,数据库产品事业部负责人,ACM、CCF、IEEE会士(Fellow)李飞飞发表《从数据到智能:Data+AI驱动的云原生数据库》主题演讲。他表示,数据是生成式AI的核心资产,大模型时代的数据管理系统需具备多模处理和实时分析能力。阿里云瑶池将数据+AI全面融合,构建一站式多模数据管理平台,以数据驱动决策与创新,为用户提供像“搭积木”一样易用、好用、高可用的使用体验。
云栖重磅|从数据到智能:Data+AI驱动的云原生数据库
|
1月前
|
SQL 关系型数据库 数据库
国产数据实战之docker部署MyWebSQL数据库管理工具
【10月更文挑战第23天】国产数据实战之docker部署MyWebSQL数据库管理工具
105 4
国产数据实战之docker部署MyWebSQL数据库管理工具
|
25天前
|
SQL 存储 Linux
从配置源到数据库初始化一步步教你在CentOS 7.9上安装SQL Server 2019
【11月更文挑战第8天】本文介绍了在 CentOS 7.9 上安装 SQL Server 2019 的详细步骤,包括系统准备、配置安装源、安装 SQL Server 软件包、运行安装程序、初始化数据库以及配置远程连接。通过这些步骤,您可以顺利地在 CentOS 系统上部署和使用 SQL Server 2019。
|
26天前
|
SQL 存储 Linux
从配置源到数据库初始化一步步教你在CentOS 7.9上安装SQL Server 2019
【11月更文挑战第7天】本文介绍了在 CentOS 7.9 上安装 SQL Server 2019 的详细步骤,包括系统要求检查与准备、配置安装源、安装 SQL Server 2019、配置 SQL Server 以及数据库初始化(可选)。通过这些步骤,你可以成功安装并初步配置 SQL Server 2019,进行简单的数据库操作。
|
1月前
|
SQL 存储 缓存
SQL Server 数据太多如何优化
11种优化方案供你参考,优化 SQL Server 数据库性能得从多个方面着手,包括硬件配置、数据库结构、查询优化、索引管理、分区分表、并行处理等。通过合理的索引、查询优化、数据分区等技术,可以在数据量增大时保持较好的性能。同时,定期进行数据库维护和清理,保证数据库高效运行。
|
1月前
|
关系型数据库 分布式数据库 数据库
云栖大会|从数据到决策:AI时代数据库如何实现高效数据管理?
在2024云栖大会「海量数据的高效存储与管理」专场,阿里云瑶池讲师团携手AMD、FunPlus、太美医疗科技、中石化、平安科技以及小赢科技、迅雷集团的资深技术专家深入分享了阿里云在OLTP方向的最新技术进展和行业最佳实践。
|
2月前
|
存储 数据挖掘 数据库
数据库数据恢复—SQLserver数据库ndf文件大小变为0KB的数据恢复案例
一个运行在存储上的SQLServer数据库,有1000多个文件,大小几十TB。数据库每10天生成一个NDF文件,每个NDF几百GB大小。数据库包含两个LDF文件。 存储损坏,数据库不可用。管理员试图恢复数据库,发现有数个ndf文件大小变为0KB。 虽然NDF文件大小变为0KB,但是NDF文件在磁盘上还可能存在。可以尝试通过扫描&拼接数据库碎片来恢复NDF文件,然后修复数据库。
|
2月前
|
人工智能 Cloud Native 容灾
云数据库“再进化”,OB Cloud如何打造云时代的数据底座?
云数据库“再进化”,OB Cloud如何打造云时代的数据底座?
|
25天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法,包括安装并连接MySQL服务器、选择数据库、执行SQL语句(如插入、更新、删除和查询),以及将结果集返回到数组。通过具体示例代码,详细介绍了每一步的操作流程,帮助读者快速入门PHP与MySQL的交互。
34 1
|
28天前
|
SQL 关系型数据库 MySQL
go语言数据库中mysql驱动安装
【11月更文挑战第2天】
38 4