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