批量清理指定数据库中所有数据--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;