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

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

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

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

--Removealldatafromadatabase

SETNOCOUNTON
--Tablestoignore
DECLARE@IgnoreTables
TABLE(TableNamevarchar(512))
INSERTINTO@IgnoreTables(TableName)VALUES('sysdiagrams')
DECLARE@AllRelationships
TABLE(ForeignKeyvarchar(512)
,TableNamevarchar(512)
,ColumnNamevarchar(512)
,ReferenceTableNamevarchar(512)
,ReferenceColumnNamevarchar(512)
,DeleteRulevarchar(512))
INSERTINTO@AllRelationships
SELECTf.nameASForeignKey,
OBJECT_NAME(f.parent_object_id)ASTableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id)ASColumnName,
OBJECT_NAME(f.referenced_object_id)ASReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id)ASReferenceColumnName,
delete_referential_action_descasDeleteRule
FROMsys.foreign_keysASf
INNERJOINsys.foreign_key_columnsASfc
ONf.OBJECT_ID=fc.constraint_object_id


DECLARE@TableOwnervarchar(512)
DECLARE@TableNamevarchar(512)
DECLARE@ForeignKeyvarchar(512)
DECLARE@ColumnNamevarchar(512)
DECLARE@ReferenceTableNamevarchar(512)
DECLARE@ReferenceColumnNamevarchar(512)
DECLARE@DeleteRulevarchar(512)


PRINT('LoopthroughalltablesandswitchallconstraintstohaveadeleteruleofCASCADE')
DECLAREDataBaseTables0
CURSORFOR
SELECTSCHEMA_NAME(t.schema_id)ASschema_name,t.nameAStable_name
FROMsys.tablesASt;

OPENDataBaseTables0;

FETCHNEXTFROMDataBaseTables0
INTO@TableOwner,@TableName;

WHILE@@FETCH_STATUS=0
BEGIN
IF(NOTEXISTS(SELECTTOP11FROM@IgnoreTablesWHERETableName=@TableName))
BEGIN
PRINT'['+@TableOwner+'].['+@TableName+']';

DECLAREDataBaseTableRelationshipsCURSORFOR
SELECTForeignKey,ColumnName,ReferenceTableName,ReferenceColumnName
FROM@AllRelationships
WHERETableName=@TableName

OPENDataBaseTableRelationships;
FETCHNEXTFROMDataBaseTableRelationshipsINTO@ForeignKey,@ColumnName,@ReferenceTableName,@ReferenceColumnName;

IF@@FETCH_STATUS<>0
PRINT'=====>NoRelationships';

WHILE@@FETCH_STATUS=0
BEGIN
PRINT'=====>switchingdeleteruleon'+@ForeignKey+'toCASCADE';
BEGINTRANSACTION
BEGINTRY
EXEC('

ALTERTABLE['+@TableOwner+'].['+@TableName+']
DROPCONSTRAINT'+@ForeignKey+';

ALTERTABLE['+@TableOwner+'].['+@TableName+']ADDCONSTRAINT
'+@ForeignKey+'FOREIGNKEY
(
'+@ColumnName+'
)REFERENCES'+@ReferenceTableName+'
(
'+@ReferenceColumnName+'
)ONDELETECASCADE;
');
COMMITTRANSACTION
ENDTRY
BEGINCATCH
PRINT'=====>can''tswitch'+@ForeignKey+'toCASCADE,-'+
CAST(ERROR_NUMBER()ASVARCHAR)+'-'+ERROR_MESSAGE();
ROLLBACKTRANSACTION
ENDCATCH;

FETCHNEXTFROMDataBaseTableRelationshipsINTO@ForeignKey,@ColumnName,@ReferenceTableName,@ReferenceColumnName;
END;

CLOSEDataBaseTableRelationships;
DEALLOCATEDataBaseTableRelationships;

END
PRINT'';
PRINT'';

FETCHNEXTFROMDataBaseTables0
INTO@TableOwner,@TableName;
END
CLOSEDataBaseTables0;
DEALLOCATEDataBaseTables0;

PRINT('LoopthougheachtableandDELETEAlldatafromthetable')

DECLAREDataBaseTables1CURSORFOR
SELECTSCHEMA_NAME(t.schema_id)ASschema_name,t.nameAStable_name
FROMsys.tablesASt;

OPENDataBaseTables1;

FETCHNEXTFROMDataBaseTables1
INTO@TableOwner,@TableName;

WHILE@@FETCH_STATUS=0
BEGIN
IF(NOTEXISTS(SELECTTOP11FROM@IgnoreTablesWHERETableName=@TableName))
BEGIN
PRINT'['+@TableOwner+'].['+@TableName+']';
PRINT'=====>deletingdatafrom['+@TableOwner+'].['+@TableName+']';
BEGINTRY
EXEC('
DELETEFROM['+@TableOwner+'].['+@TableName+']
DBCCCHECKIDENT(['+@TableName+'],RESEED,0)
');
ENDTRY
BEGINCATCH
PRINT'=====>can''tFROM['+@TableOwner+'].['+@TableName+'],-'+
CAST(ERROR_NUMBER()ASVARCHAR)+'-'+ERROR_MESSAGE();
ENDCATCH;
END

PRINT'';
PRINT'';

FETCHNEXTFROMDataBaseTables1
INTO@TableOwner,@TableName;
END
CLOSEDataBaseTables1;
DEALLOCATEDataBaseTables1;

PRINT('Loopthroughalltablesandswitchallconstraintstohaveadeleteruletheyhadatthebegginingofthetask')

DECLAREDataBaseTables2CURSORFOR
SELECTSCHEMA_NAME(t.schema_id)ASschema_name,t.nameAStable_name
FROMsys.tablesASt;
OPENDataBaseTables2;

FETCHNEXTFROMDataBaseTables2
INTO@TableOwner,@TableName;

WHILE@@FETCH_STATUS=0
BEGIN

IF(NOTEXISTS(SELECTTOP11FROM@IgnoreTablesWHERETableName=@TableName))
BEGIN
PRINT'['+@TableOwner+'].['+@TableName+']';

DECLAREDataBaseTableRelationshipsCURSORFOR
SELECTForeignKey,ColumnName,ReferenceTableName,ReferenceColumnName,DeleteRule
FROM@AllRelationships
WHERETableName=@TableName

OPENDataBaseTableRelationships;
FETCHNEXTFROMDataBaseTableRelationshipsINTO@ForeignKey,@ColumnName,@ReferenceTableName,@ReferenceColumnName,@DeleteRule;

IF@@FETCH_STATUS<>0
PRINT'=====>NoRelationships';

WHILE@@FETCH_STATUS=0
BEGIN
DECLARE@switchBackTovarchar(50)=
CASE
WHEN@DeleteRule='NO_ACTION'THEN'NOACTION'
WHEN@DeleteRule='CASCADE'THEN'CASCADE'
WHEN@DeleteRule='SET_NULL'THEN'SETNULL'
WHEN@DeleteRule='SET_DEFAULT'THEN'SETDEFAULT'
END

PRINT'=====>switchingdeleteruleon'+@ForeignKey+'to'+@switchBackTo;

BEGINTRANSACTION
BEGINTRY
EXEC('

ALTERTABLE['+@TableOwner+'].['+@TableName+']
DROPCONSTRAINT'+@ForeignKey+';

ALTERTABLE['+@TableOwner+'].['+@TableName+']ADDCONSTRAINT
'+@ForeignKey+'FOREIGNKEY
(
'+@ColumnName+'
)REFERENCES'+@ReferenceTableName+'
(
'+@ReferenceColumnName+'
)ONDELETE'+@switchBackTo+'
');

COMMITTRANSACTION
ENDTRY
BEGINCATCH
PRINT'=====>can''tchange'+@ForeignKey+'backto'+@switchBackTo+',-'+
CAST(ERROR_NUMBER()ASVARCHAR)+'-'+ERROR_MESSAGE();
ROLLBACKTRANSACTION
ENDCATCH;

FETCHNEXTFROMDataBaseTableRelationships
INTO@ForeignKey,@ColumnName,@ReferenceTableName,@ReferenceColumnName,@DeleteRule;
END;

CLOSEDataBaseTableRelationships;
DEALLOCATEDataBaseTableRelationships;

END
PRINT'';
PRINT'';

FETCHNEXTFROMDataBaseTables2
INTO@TableOwner,@TableName;
END
CLOSEDataBaseTables2;

DEALLOCATEDataBaseTables2;

© 2011EricHu

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

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

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

作者：EricHu（DB、C\S、B\S、WebService、WCF、PM等）
出处：http://www.cnblogs.com/huyong/

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