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

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

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

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

-- Removealldatafromadatabase

SETNOCOUNT ON
-- Tablestoignore
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
SELECTf.name ASForeignKey,
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_desc asDeleteRule
FROMsys.foreign_keys ASf
INNER JOINsys.foreign_key_columns ASfc
ONf. 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( ' LoopthroughalltablesandswitchallconstraintstohaveadeleteruleofCASCADE ')
DECLAREDataBaseTables0
CURSOR FOR
SELECTSCHEMA_NAME(t.schema_id) ASschema_name,t.name AStable_name
FROMsys.tables ASt;

OPENDataBaseTables0;

FETCH NEXT FROMDataBaseTables0
INTO @TableOwner, @TableName;

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

DECLAREDataBaseTableRelationships CURSOR FOR
SELECTForeignKey,ColumnName,ReferenceTableName,ReferenceColumnName
FROM @AllRelationships
WHERETableName = @TableName

OPENDataBaseTableRelationships;
FETCH NEXT FROMDataBaseTableRelationships INTO @ForeignKey, @ColumnName, @ReferenceTableName, @ReferenceColumnName;

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

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

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

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

FETCH NEXT FROMDataBaseTableRelationships INTO @ForeignKey, @ColumnName, @ReferenceTableName, @ReferenceColumnName;
END;

CLOSEDataBaseTableRelationships;
DEALLOCATEDataBaseTableRelationships;

END
PRINT '';
PRINT '';

FETCH NEXT FROMDataBaseTables0
INTO @TableOwner, @TableName;
END
CLOSEDataBaseTables0;
DEALLOCATEDataBaseTables0;

PRINT( ' LoopthougheachtableandDELETEAlldatafromthetable ')

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

OPENDataBaseTables1;

FETCH NEXT FROMDataBaseTables1
INTO @TableOwner, @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
IF( NOT EXISTS( SELECT TOP 1 1 FROM @IgnoreTables WHERETableName = @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() AS VARCHAR) + ' - ' +ERROR_MESSAGE();
ENDCATCH;
END

PRINT '';
PRINT '';

FETCH NEXT FROMDataBaseTables1
INTO @TableOwner, @TableName;
END
CLOSEDataBaseTables1;
DEALLOCATEDataBaseTables1;

PRINT( ' Loopthroughalltablesandswitchallconstraintstohaveadeleteruletheyhadatthebegginingofthetask ')

DECLAREDataBaseTables2 CURSOR FOR
SELECTSCHEMA_NAME(t.schema_id) ASschema_name,t.name AStable_name
FROMsys.tables ASt;
OPENDataBaseTables2;

FETCH NEXT FROMDataBaseTables2
INTO @TableOwner, @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN

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

DECLAREDataBaseTableRelationships CURSOR FOR
SELECTForeignKey,ColumnName,ReferenceTableName,ReferenceColumnName,DeleteRule
FROM @AllRelationships
WHERETableName = @TableName

OPENDataBaseTableRelationships;
FETCH NEXT FROMDataBaseTableRelationships INTO @ForeignKey, @ColumnName, @ReferenceTableName, @ReferenceColumnName, @DeleteRule;

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

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @switchBackTo varchar( 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;

BEGIN TRANSACTION
BEGINTRY
EXEC( '

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

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

COMMIT TRANSACTION
ENDTRY
BEGINCATCH
PRINT ' =====>can '' tchange ' + @ForeignKey + ' backto ' + @switchBackTo + ' ,- ' +
CAST(ERROR_NUMBER() AS VARCHAR) + ' - ' +ERROR_MESSAGE();
ROLLBACK TRANSACTION
ENDCATCH;

FETCH NEXT FROMDataBaseTableRelationships
INTO @ForeignKey, @ColumnName, @ReferenceTableName, @ReferenceColumnName, @DeleteRule;
END;

CLOSEDataBaseTableRelationships;
DEALLOCATEDataBaseTableRelationships;

END
PRINT '';
PRINT '';

FETCH NEXT FROMDataBaseTables2
INTO @TableOwner, @TableName;
END
CLOSEDataBaseTables2;

DEALLOCATEDataBaseTables2;

复制代码

© 2011EricHu

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