不说废话,大牛初始亦菜鸟,成功之路贵执行,马上行动!如果想获得更优质的显示效果,请在Chrome、Firefox等现代浏览器浏览本站。

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

:: 数据库精典 EricHu 881℃ 0评论

[推荐] (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 =====> cant 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 =====> cant 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 =====> cant 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 » [推荐] (SqlServer)批量清理指定数据库中所有数据

喜欢 (0)or分享 (0)
发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址