(SqlServer)批量清理指定数据库中所有数据
- WBOYOriginal
- 2016-06-07 15:51:391297browse
欢迎进入Windows社区论坛，与300万技术人员互动交流 >>进入 COMMIT TRANSACTION END TRY BEGIN CATCH PRINT '===== can''t switch ' + @ForeignKey + ' to CASCADE, - ' + CAST（ERROR_NUMBER（） AS VARCHAR） + ' - ' + ERROR_MESSAGE（）； ROLLBACK TRAN
欢迎进入Windows社区论坛，与300万技术人员互动交流 >>进入
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT '=====> can''t 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 '=====> can''t 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
[1] [2] [3]
