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

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

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

（编辑：李大同）

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