[推荐] (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;