(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;  

© 2011  EricHu

原创作品，转贴请注明作者和出处，留此信息。

 

------------------------------------------------

cnBlobs:http://www.cnblogs.com/huyong/
CSDN：http://blog.csdn.net/chinahuyong 

 

作者：EricHu（DB、C\S、B\S、WebService、WCF、PM等）
出处：http://www.cnblogs.com/huyong/

Q Q：80368704   E-Mail: 80368704@qq.com
本博文欢迎大家浏览和转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，在『参考』的文章中，我会表明参考的文章来源，尊重他人版权。若您发现我侵犯了您的版权，请及时与我联系。
更多文章请看 [置顶]索引贴——（不断更新中）