[转载]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 =====> cant switch + @ForeignKey + to CASCADE, – +
CAST(ERROR_NUMBER() AS VARCHAR+ + ERROR_MESSAGE();
ROLLBACK TRANSACTION
END CATCH;

FETCH NEXT FROM DataBaseTableRelationships INTO @ForeignKey@ColumnName@ReferenceTableName@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 =====> cant 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, ColumnName, ReferenceTableName, ReferenceColumnName, DeleteRule
FROM @AllRelationships
WHERE TableName = @TableName

OPEN DataBaseTableRelationships;
FETCH NEXT FROM DataBaseTableRelationships INTO @ForeignKey@ColumnName@ReferenceTableName@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 =====> cant change +@ForeignKey + back to + @switchBackTo +, – +
CAST(ERROR_NUMBER() AS VARCHAR+ + ERROR_MESSAGE();
ROLLBACK TRANSACTION
END CATCH;

FETCH NEXT FROM DataBaseTableRelationships
INTO @ForeignKey@ColumnName@ReferenceTableName@ReferenceColumnName@DeleteRule;
END;

CLOSE DataBaseTableRelationships;
DEALLOCATE DataBaseTableRelationships;

END
PRINT ;
PRINT ;

FETCH NEXT FROM DataBaseTables2
INTO @TableOwner,@TableName;
END
CLOSE DataBaseTables2;

DEALLOCATE DataBaseTables2;

© 2011 EricHu

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

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏