[推荐] (SqlServer)批量清理指定数据库中所有数据
发布时间：2020-12-12 14:47:01 所属栏目：MsSql教程 来源：网络整理
导读：[ 推荐 ] ? (SqlServer) 批量清理指定数据库中所有数据 —— 通过知识共享树立个人品牌。 在实际应用中，当我们准备把一个项目移交至客户手中使用时，我们需要把库中所有表先前的测试数据清空，以给客户一个干净的数据库，如果涉及的表很多，要一一的清空，
|
[推荐]?(SqlServer)批量清理指定数据库中所有数据 ——通过知识共享树立个人品牌。 在实际应用中，当我们准备把一个项目移交至客户手中使用时，我们需要把库中所有表先前的测试数据清空，以给客户一个干净的数据库，如果涉及的表很多，要一一的清空，不仅花费时间，还容易出错以及漏删，在这儿我提供了一个方法，可快捷有效的清空指定数据库所有表的数据。仅供参考，欢迎交流不同意见。 ? -- Remove?all?data?from?a?databaseSET?NOCOUNT? ON Tables?to?ignore DECLARE? @IgnoreTables? ???????? TABLE?(TableName? varchar( 512)) INSERT? INTO? @IgnoreTables?(TableName)? VALUES?( ' sysdiagrams ') @AllRelationships? ???????? TABLE?(ForeignKey? varchar( 512) ??????????????,TableName? @AllRelationships SELECT?f.name? AS?ForeignKey, OBJECT_NAME(f.parent_object_id)? AS?TableName,255)">COL_NAME(fc.parent_object_id, fc.parent_column_id)? AS?ColumnName,255)">OBJECT_NAME?(f.referenced_object_id)? AS?ReferenceTableName,255)">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 ? @TableOwner? varchar( 512) @TableName? @ForeignKey? @ColumnName? @ReferenceTableName? @ReferenceColumnName? @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? @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? FOR? ???????? SELECT?ForeignKey,?ColumnName,?ReferenceTableName,?ReferenceColumnName ???????? @TableName ???????? OPEN?DataBaseTableRelationships; ???????? FROM?DataBaseTableRelationships? @ForeignKey,? @ColumnName,0)">@ReferenceTableName,0)">@ReferenceColumnName; ???????? IF? @@FETCH_STATUS? <>? 0? ???????????? =====>?No?Relationships '?;? ???????? =? 0 ???????? BEGIN ???????????? =====>?switching?delete?rule?on? @ForeignKey? ?to?CASCADE '; ???????????? BEGIN? TRANSACTION ???????????? BEGIN?TRY ???????????????? EXEC( ????????????????ALTER?TABLE?[ ] ?????????????????DROP?CONSTRAINT? @ForeignKey ; ????????????????ALTER?TABLE?[ ]?ADD?CONSTRAINT ???????????????? ?FOREIGN?KEY ????????????????( ???????????????? @ColumnName ????????????????)?REFERENCES? @ReferenceTableName ????????????????( ???????????????? @ReferenceColumnName ????????????????)?ON?DELETE?CASCADE; ???????????????? '); ???????????????? COMMIT? END?TRY ???????????? BEGIN?CATCH ???????????????? =====>?can '' t?switch? ?to?CASCADE,?-? + ???????????????? CAST(ERROR_NUMBER()? AS? VARCHAR)? ?-? +?ERROR_MESSAGE(); ???????????????? ROLLBACK? END?CATCH; ???????????? ???????????? @ReferenceColumnName; ???????? END; ???????? CLOSE?DataBaseTableRelationships; ???????? DEALLOCATE?DataBaseTableRelationships; ???????? END ???????? ''; ???????? ''; ???????? FROM?DataBaseTables0? ???????? @TableName; ???? END CLOSE?DataBaseTables0; DEALLOCATE?DataBaseTables0; Loop?though?each?table?and?DELETE?All?data?from?the?table ') DECLARE?DataBaseTables1? OPEN?DataBaseTables1;? FROM?DataBaseTables1? '; ???????? =====>?deleting?data?from?[ BEGIN?TRY ???????????? ?????????????????DELETE?FROM?[ ] ?????????????????DBCC?CHECKIDENT?([ ],?RESEED,?0) ????????????????? '); ???????? END?TRY ???????? BEGIN?CATCH ???????????? t?FROM?[ + ?????????????????? +?ERROR_MESSAGE(); ???????? END?CATCH; ???? END ????? ???? ''; ???? ''; ????? ???? FROM?DataBaseTables1? ???? @TableName; CLOSE?DataBaseTables1; DEALLOCATE?DataBaseTables1;? ? Loop?through?all?tables?and?switch?all?constraints?to?have?a?delete?rule?they?had?at?the?beggining?of?the?task DECLARE?DataBaseTables2? AS?t; OPEN?DataBaseTables2;? FROM?DataBaseTables2? BEGIN ? ???? BEGIN ???? '; ???? FOR? ???? @AllRelationships? ???? @TableName ???? OPEN?DataBaseTableRelationships; ???? @ReferenceColumnName,0)">@DeleteRule; ???? <>? 0? ???? '?;? ???? =? 0 ???? @switchBackTo? varchar( 50)? = ???????? CASE? ???????????? WHEN? @DeleteRule? =? NO_ACTION '? THEN? NO?ACTION ' ???????????? CASCADE SET_NULL SET?NULL SET_DEFAULT SET?DEFAULT ' ???????? END? ???????? ?to? @switchBackTo; ???????? TRANSACTION ???????? ????????????ALTER?TABLE?[ ] ????????????DROP?CONSTRAINT? ; ????????????ALTER?TABLE?[ ]?ADD?CONSTRAINT ???????????? ?FOREIGN?KEY ????????????( ???????????? ????????????)?REFERENCES? ????????????( ???????????? ????????????)?ON?DELETE? @switchBackTo ???????????? '); ???????????? ???????????? t?change? ?back?to? @switchBackTo? ,128)">+ ???????????? +?ERROR_MESSAGE(); ???????????? END?CATCH; ???????? FROM?DataBaseTableRelationships? ???????? @DeleteRule; ???? END; ???? CLOSE?DataBaseTableRelationships; ???? DEALLOCATE?DataBaseTableRelationships; ???? END ???? ''; ???? FROM?DataBaseTables2? ???? CLOSE?DataBaseTables2; DEALLOCATE?DataBaseTables2;??
? 2011??EricHu 原创作品，转贴请注明作者和出处，留此信息。 ? ------------------------------------------------ cnBlobs:http://www.cnblogs.com/huyong/ ? 作者：EricHu（DB、CS、BSWebServiceWCFPM等） Q Q：80368704?? E-Mail: 80368704@qq.com本博文欢迎大家浏览和转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，在『参考』的文章中，我会表明参考的文章来源，尊重他人版权。若您发现我侵犯了您的版权，请及时与我联系。更多文章请看?[置顶]索引贴——（不断更新中） （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |