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

[ 推荐 ] (SqlServer) 批量清理指定数据库中所有数据 通过知识共享树立个人品牌。 在实际应用中，当我们准备把一个项目移交至客户手中使用时，我们需要把库中所有表先前的测试数据清空，以给客户一个干净的数据库，如果涉及的表很多，要一一的清空，不仅花费

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

——通过知识共享树立个人品牌。

天意易趣网拍卖系统

前台主要功能：首选服务 注销登陆 查看使用帮助 修改添加登陆帐号拍卖商品管理 管理拍卖商品 推荐拍卖商品 删除特定拍卖 已经结束商品 拍卖分类管理 新闻管理 添加文章 删除修改 栏目管理 新闻CSS设定 新闻JS生成 初始化新闻 参数设置 用户管理 未审核用户管理 普通用户管理 高级用户管理 黄金用户管理 管理所有用户 数据库管理 压缩数据库 备份数据库 恢复数据库 批量处理 系统指标测试V1.

0

查看详情

    在实际应用中，当我们准备把一个项目移交至客户手中使用时，我们需要把库中所有表先前的测试数据清空，以给客户一个干净的数据库，如果涉及的表很多，要一一的清空，不仅花费时间，还容易出错以及漏删，在这儿我提供了一个方法，可快捷有效的清空指定数据库所有表的数据。仅供参考，欢迎交流不同意见。

--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;