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