求SQL Server中Test1库全表按[deleted]=1删数据的动态SQL及回退方案

Hey there! Let's break down how to safely handle this production-ready dynamic SQL task, including row counting and rollback strategies—since messing with production data requires extra care.

This script will iterate through all tables in the Test1 database that have a [deleted] column, delete the qualifying rows, and track how many rows were removed from each table:

USE Test1;
GO

DECLARE @TableName NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);
DECLARE @DeleteCounts TABLE (TableName NVARCHAR(128), DeletedRows INT);

-- Cursor to loop through all tables containing the [deleted] column
DECLARE TableCursor CURSOR FOR
SELECT t.name
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.name = 'deleted'
ORDER BY t.name;

OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Build dynamic delete statement, capture row count, and log it
    SET @SQL = N'
    DECLARE @RowCount INT;
    DELETE FROM ' + QUOTENAME(@TableName) + N'
    WHERE [deleted] = 1;
    SET @RowCount = @@ROWCOUNT;
    INSERT INTO @DeleteCounts (TableName, DeletedRows)
    VALUES (''' + @TableName + N''', @RowCount);';

    -- Execute the dynamic SQL
    EXEC sp_executesql @SQL;

    FETCH NEXT FROM TableCursor INTO @TableName;
END

CLOSE TableCursor;
DEALLOCATE TableCursor;

-- Output the final delete statistics
SELECT TableName, DeletedRows FROM @DeleteCounts;
GO

Key Notes: #

• QUOTENAME() ensures we handle tables with special characters (like spaces or reserved words) safely, avoiding SQL injection risks.
• @@ROWCOUNT captures exactly how many rows were deleted per table, which we store in a temporary table to report at the end.

Since this is production, you need fail-safes. Here are two reliable approaches:

1. Pre-Delete Data Backup (Recommended) #

Before running any deletes, back up all [deleted]=1 rows to dedicated backup tables. This lets you restore data even if you commit the delete:

USE Test1;
GO

DECLARE @TableName NVARCHAR(128);
DECLARE @BackupSQL NVARCHAR(MAX);
DECLARE @BackupSuffix NVARCHAR(20) = N'_DeletedBackup_' + CONVERT(NVARCHAR(8), GETDATE(), 112); -- YYYYMMDD suffix

DECLARE BackupCursor CURSOR FOR
SELECT t.name
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.name = 'deleted'
ORDER BY t.name;

OPEN BackupCursor;
FETCH NEXT FROM BackupCursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Create backup table if it doesn't exist, then insert the rows to delete
    SET @BackupSQL = N'
    IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = ''' + @TableName + @BackupSuffix + N''')
    BEGIN
        SELECT * INTO ' + QUOTENAME(@TableName + @BackupSuffix) + N'
        FROM ' + QUOTENAME(@TableName) + N'
        WHERE [deleted] = 1;
    END
    ELSE
    BEGIN
        INSERT INTO ' + QUOTENAME(@TableName + @BackupSuffix) + N'
        SELECT * FROM ' + QUOTENAME(@TableName) + N'
        WHERE [deleted] = 1;
    END;';

    EXEC sp_executesql @BackupSQL;

    FETCH NEXT FROM BackupCursor INTO @TableName;
END

CLOSE BackupCursor;
DEALLOCATE BackupCursor;
GO

To restore data later, just run:

INSERT INTO [YourOriginalTableName]
SELECT * FROM [YourBackupTableName];

2. Transaction-Wrapped Deletes (For Immediate Rollback) #

Wrap the delete logic in a transaction so you can preview results before committing. If something looks off, roll back immediately:

USE Test1;
GO

BEGIN TRANSACTION;

DECLARE @TableName NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);
DECLARE @DeleteCounts TABLE (TableName NVARCHAR(128), DeletedRows INT);

DECLARE TableCursor CURSOR FOR
SELECT t.name
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.name = 'deleted'
ORDER BY t.name;

OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = N'
    DECLARE @RowCount INT;
    DELETE FROM ' + QUOTENAME(@TableName) + N'
    WHERE [deleted] = 1;
    SET @RowCount = @@ROWCOUNT;
    INSERT INTO @DeleteCounts (TableName, DeletedRows)
    VALUES (''' + @TableName + N''', @RowCount);';

    EXEC sp_executesql @SQL;

    FETCH NEXT FROM TableCursor INTO @TableName;
END

CLOSE TableCursor;
DEALLOCATE TableCursor;

-- Review the delete statistics first!
SELECT TableName, DeletedRows FROM @DeleteCounts;

-- If everything looks correct:
-- COMMIT TRANSACTION;

-- If you need to undo the deletes:
-- ROLLBACK TRANSACTION;

Important: #

• Transactions will lock tables during execution, so run this during low-traffic periods to avoid impacting production workloads.

Before running any deletes, validate the data you're about to remove:

USE Test1;
GO

DECLARE @SQL NVARCHAR(MAX) = N'';

-- Build a query to count rows to delete per table
SELECT @SQL += N'UNION ALL SELECT ''' + QUOTENAME(t.name) + N''' AS TableName, COUNT(*) AS RowsToDelete FROM ' + QUOTENAME(t.name) + N' WHERE [deleted] = 1 '
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.name = 'deleted';

-- Remove the leading UNION ALL
SET @SQL = STUFF(@SQL, 1, 10, '');

-- Execute to see how many rows will be deleted
EXEC sp_executesql @SQL;

This gives you a clear picture of the data volume before making any changes.

内容的提问来源于stack exchange，提问作者Ash Atre