[推荐] (SqlServer)批量清理指定数据库中所有数据

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/chinahuyong/article/details/7205159

[推荐] (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/
CSDNhttp://blog.csdn.net/chinahuyong 

 

作者:EricHuDBC\SB\SWebServiceWCFPM等)
出处:http://www.cnblogs.com/huyong/

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

 

 

上一篇:大学辍学、自学编程,GitHub创始人是怎么号召2800万程序员的?


下一篇:Linux学习笔记:sudo、tcp_wrapper、pam