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

[ 推荐 ] (SqlServer) 批量清理指定数据库中所有数据 通过知识共享树立个人品牌。 在实际应用中，当我们准备把一个项目移交至客户手中使用时，我们需要把库中所有表先前的测试数据清空，以给客户一个干净的数据库，如果涉及的表很多，要一一的清空，不仅花费 [推荐] (SqlServer)批量清理指定数据库中所有数据——通过知识共享树立个人品牌。 在实际应用中，当我们准备把一个项目移交至客户手中使用时，我们需要把库中所有表先前的测试数据清空，以给客户一个干净的数据库，如果涉及的表很多，要一一的清空，不仅花费时间，还容易出错以及漏删，在这儿我提供了一个方法，可快捷有效的清空指定数据库所有表的数据。仅供参考，欢迎交流不同意见。 --Remove all data from a databaseSET NOCOUNT ON--Tables to ignoreDECLARE @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 @AllRelationshipsSELECT 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 DeleteRuleFROM sys.foreign_keys AS fINNER JOIN sys.foreign_key_columns AS fcON 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_nameFROM sys.tables AS t;OPEN DataBaseTables0; FETCH NEXT FROM DataBaseTables0 INTO @TableOwner,@TableName;WHILE @@FETCH_STATUS = 0BEGIN 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 '=====> 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; ENDCLOSE 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_nameFROM sys.tables AS t;OPEN DataBaseTables1; FETCH NEXT FROM DataBaseTables1 INTO @TableOwner,@TableName;WHILE @@FETCH_STATUS = 0BEGIN 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;ENDCLOSE 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_nameFROM sys.tables AS t;OPEN DataBaseTables2; FETCH NEXT FROM DataBaseTables2 INTO @TableOwner,@TableName;WHILE @@FETCH_STATUS = 0BEGIN 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 '=====> can''t 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;ENDCLOSE DataBaseTables2;DEALLOCATE DataBaseTables2;