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_columnsAS fc
ON f.OBJECT_ID= fc.constraint_object_id


DECLARE @TableOwnervarchar(512)
DECLARE @TableNamevarchar(512)
DECLARE @ForeignKeyvarchar(512)
DECLARE @ColumnNamevarchar(512)
DECLARE @ReferenceTableNamevarchar(512)
DECLARE @ReferenceColumnNamevarchar(512)
DECLARE @DeleteRulevarchar(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 TOP1 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 NEXTFROM 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 NEXTFROM DataBaseTableRelationships INTO @ForeignKey, @ColumnName, @ReferenceTableName, @ReferenceColumnName;
        END;

        CLOSE DataBaseTableRelationships;
        DEALLOCATE DataBaseTableRelationships;

        END
        PRINT '';
        PRINT '';

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