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

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

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

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

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

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

-- Removealldatafromadatabase

SETNOCOUNT ON
Tablestoignore
DECLARE @IgnoreTables
TABLE(TableName varchar( 512))
INSERT INTO @IgnoreTables(TableName) VALUES( ' sysdiagrams ')
@AllRelationships
TABLE(ForeignKey varchar( 512)
,TableName SELECTf.name ASForeignKey,
OBJECT_NAME(f.parent_object_id) ASTableName,255);">COL_NAME(fc.parent_object_id,
fc.parent_column_id) ASColumnName,255);">OBJECT_NAME(f.referenced_object_id) ASReferenceTableName,255);">COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) ASReferenceColumnName,
delete_referential_action_desc asDeleteRule
FROMsys.foreign_keys ASf
INNER JOINsys.foreign_key_columns ASfc
ONf. OBJECT_ID =fc.constraint_object_id

@TableOwner varchar( 512)
@TableName @ForeignKey @ColumnName @ReferenceTableName @ReferenceColumnName @DeleteRule varchar( 512)

PRINT( LoopthroughalltablesandswitchallconstraintstohaveadeleteruleofCASCADE DECLAREDataBaseTables0
CURSOR FOR
SELECTSCHEMA_NAME(t.schema_id) ASschema_name,t.name AStable_name
FROMsys.tables ASt;

OPENDataBaseTables0;

FETCH NEXT FROMDataBaseTables0
@TableOwner,@TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
IF( NOT EXISTS( SELECT TOP 11 FROM @IgnoreTables WHERETableName = @TableName))
PRINT [ ' + @TableOwner + ].[ @TableName ] ';

DECLAREDataBaseTableRelationships SELECTForeignKey,ColumnName,ReferenceTableName,ReferenceColumnName
@TableName

OPENDataBaseTableRelationships;
FROMDataBaseTableRelationships @ForeignKey,0);">@ColumnName,0);">@ReferenceTableName,0);">@ReferenceColumnName;

IF @@FETCH_STATUS <> 0
=====>NoRelationships =====>switchingdeleteruleon @ForeignKey toCASCADE ';
BEGIN TRANSACTION
BEGINTRY
EXEC(

ALTERTABLE[ ]
DROPCONSTRAINT ;

ALTERTABLE[ ]ADDCONSTRAINT
FOREIGNKEY
(
@ColumnName
)REFERENCES @ReferenceTableName
(
@ReferenceColumnName
)ONDELETECASCADE;
');
COMMIT ENDTRY
BEGINCATCH
=====>can '' tswitch toCASCADE,- +
CAST(ERROR_NUMBER() AS VARCHAR) - +ERROR_MESSAGE();
ROLLBACK ENDCATCH;

@ReferenceColumnName;
END;

CLOSEDataBaseTableRelationships;
DEALLOCATEDataBaseTableRelationships;

END
'';
'';

@TableName;
CLOSEDataBaseTables0;
DEALLOCATEDataBaseTables0;

LoopthougheachtableandDELETEAlldatafromthetable ')

DECLAREDataBaseTables1 OPENDataBaseTables1;

FROMDataBaseTables1
=====>deletingdatafrom[
DELETEFROM[ ]
DBCCCHECKIDENT([ ],RESEED,0)
tFROM[ ENDCATCH;
END

CLOSEDataBaseTables1;
DEALLOCATEDataBaseTables1;

Loopthroughalltablesandswitchallconstraintstohaveadeleteruletheyhadatthebegginingofthetask DECLAREDataBaseTables2 ASt;
OPENDataBaseTables2;

FROMDataBaseTables2
BEGIN

@ReferenceColumnName,0);">@DeleteRule;

@switchBackTo varchar( 50) =
CASE
WHEN @DeleteRule = NO_ACTION ' THEN NOACTION '
CASCADE SET_NULL SETNULL SET_DEFAULT SETDEFAULT to @switchBackTo;

)ONDELETE @switchBackTo
');

tchange backto ,255);">FROMDataBaseTableRelationships
@DeleteRule;
CLOSEDataBaseTables2;

DEALLOCATEDataBaseTables2;

? 2011EricHu

原创作品，转贴请注明作者和出处，留此信息。

------------------------------------------------

cnBlobs:http://www.cnblogs.com/huyong/
CSDN：http://blog.csdn.net/chinahuyong

作者：EricHu（DB、CS、BSWebServiceWCFPM等）
出处：http://www.cnblogs.com/huyong/

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

（编辑：李大同）

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