SqlServer批量清理指定数据库中所有数据

发布时间：2020-12-12 14:31:38 所属栏目：MsSql教程来源：网络整理

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

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

-- 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, @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,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, @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、CS、BS、WebService、WCF、PM等）
出处：http://www.cnblogs.com/huyong/

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!