[ 推荐 ] (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 ' =====> 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
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 ;
END
CLOSE DataBaseTables2;
DEALLOCATE DataBaseTables2;
查看更多关于(SqlServer)批量清理指定数据库中所有数据的详细内容...