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

[推荐] (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;  

 

作者: RDIFramework.NET
出处:http://www.cnblogs.com/huyong/
Email:406590790@qq.com
QQ:406590790
微信:13005007127(同手机号)
框架官网:http://www.rdiframework.net/
框架官网博客:http://blog.rdiframework.net/
框架其他博客:http://blog.csdn.net/chinahuyong
               http://www.cnblogs.com/huyong
RDIFramework.NET基于全新.NET Framework与.NET Core的快速信息化系统开发、整合框架,为企业快速构建垮平台、企业级的应用提供了强大支持。
关于作者:系统架构师、信息系统项目管理师、DBA。专注于微软平台项目架构、管理和企业解决方案,多年项目开发与管理经验,曾多次组织并开发多个大型项目,在面向对象、面向服务以及数据库领域有一定的造诣。现主要从事基于 RDIFramework.NET 框架的技术开发、咨询工作,主要服务于金融、医疗卫生、铁路、电信、物流、物联网、制造、零售等行业。
如有问题或建议,请多多赐教!
本文版权归作者和CNBLOGS博客共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,如有问题,可以通过微信、邮箱、QQ等联系我,非常感谢。

posted @ 2012-01-17 08:51  .NET敏捷开发框架  阅读(6505)  评论(49编辑  收藏  举报