批量为数据库内450+张表的BusinessUnit列设置值为1的实现方案咨询

Great question! Let's break this down into two clear parts: how to batch-update the BusinessUnit column across all your tables, and whether dropping and re-adding the column is a smarter move.

You can't do this with a single UPDATE statement directly, but you can generate and execute dynamic SQL using system views like sys.tables and sys.columns to automate the process. Here are two reliable methods depending on your SQL Server version:

Option 1: Using STRING_AGG (SQL Server 2017+) #

If you're on SQL Server 2017 or later, STRING_AGG simplifies concatenating all update statements into a single batch:

DECLARE @sql NVARCHAR(MAX) = (
    SELECT STRING_AGG(
        CONCAT('UPDATE ', QUOTENAME(s.name), '.', QUOTENAME(t.name), ' SET BusinessUnit = 1;'),
        CHAR(13) + CHAR(10)
    )
    FROM sys.tables t
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    JOIN sys.columns c ON t.object_id = c.object_id
    WHERE c.name = 'BusinessUnit'
      AND c.system_type_id = TYPE_ID('int') -- Verify it's the correct int column
);

PRINT @sql; -- Always preview the generated SQL first to catch issues!
-- EXEC sp_executesql @sql; -- Uncomment this line once you've verified the output

Option 2: Using a Cursor (Older SQL Server Versions) #

For versions before 2017, a cursor loops through each table and runs the update individually:

DECLARE @schemaName NVARCHAR(128), @tableName NVARCHAR(128);
DECLARE tableCursor CURSOR FOR
    SELECT s.name, t.name
    FROM sys.tables t
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    JOIN sys.columns c ON t.object_id = c.object_id
    WHERE c.name = 'BusinessUnit'
      AND c.system_type_id = TYPE_ID('int');

OPEN tableCursor;
FETCH NEXT FROM tableCursor INTO @schemaName, @tableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @sql NVARCHAR(MAX) = CONCAT(
        'UPDATE ', QUOTENAME(@schemaName), '.', QUOTENAME(@tableName), ' SET BusinessUnit = 1;'
    );
    PRINT @sql; -- Preview first to avoid mistakes
    -- EXEC sp_executesql @sql; -- Uncomment to execute after verification
    FETCH NEXT FROM tableCursor INTO @schemaName, @tableName;
END;

CLOSE tableCursor;
DEALLOCATE tableCursor;

Critical Note: Never skip the PRINT step—previewing the generated SQL ensures you're only updating the intended tables and avoids accidental data changes.

This depends on your table sizes, environment, and existing dependencies. Let's weigh the pros and cons:

Pros of Dropping/Re-Adding: #

• Massive Performance Gain (Enterprise Edition): If you're on SQL Server Enterprise Edition (or Azure SQL Database), adding a non-null column with a constant default value is a metadata-only operation—it doesn't update every row, so it's nearly instant even for tables with millions of rows. This also ensures all future rows automatically get 1 as the default.
• Clean Consistency: Guarantees no rows are missed (though the dynamic update should also handle this, this removes human error risk).

Cons of Dropping/Re-Adding: #

• Dependency Risks: If BusinessUnit is used in indexes, triggers, stored procedures, or foreign keys, dropping the column will fail. You'll need to drop those dependencies first, then re-create them afterward—this adds complexity and downtime risk.
• Data Loss Risk: If any rows were manually updated to a value other than 1 (unlikely in your case, but possible), dropping the column will permanently lose that data.
• Schema Lock Overhead: Dropping and adding columns requires schema locks, which can block other operations on the tables during the process.

Final Recommendation: #

• Small Tables: Either approach works—dynamic SQL updates are straightforward and low-risk.
• Large Tables (100k+ rows): If you have Enterprise Edition, dropping/re-adding with a default is the better choice for performance. Just be sure to map and handle all dependencies first.
• Non-Enterprise Edition: Adding a column with a default will still require updating every row (same as the UPDATE statement), so stick with the dynamic SQL approach to avoid dependency headaches.

内容的提问来源于stack exchange，提问作者Nagib Mahfuz