将值' 1 '设置为整个数据库中所有表的特定列?

Question

我们的数据库有450多个表。最近我们将BusinessUnit int列添加到没有默认值1的Db中的每个表中。现在我们要在所有表现有行中为BusinessUnit列设置1。是否有任何方法我可以做到这一点与单一查询?对于单个表，非常简单，如下所示

update tablename set BusinessUnit=1

可能在sys.tables和sys.columns的帮助下，我们可以在一个查询中做到这一点。

或者删除所有的BusinessUnit column并重新添加默认值更好吗?

如有任何建议，我将不胜感激。

Answer 1

首先让我们从真正的您在这里遇到的问题开始。将相同的列添加到数据库中的每个表中，这无疑是一个缺陷。您不可能在每个表的每一行都需要这个列。它可以非常容易引入参考完整性问题。例如，您已经将值添加到每个表中，并且这些表可能具有关系。那么，当您将TableX中的列BusinessUnit的值更新为2时，会发生什么?但不要为所有与TableATableB和TableZ相关的行?然后是"父母"与一个BusinessUnit和"子"相关的行与另一行相关的行。这很可能不是期望或期望的行为。

大多数情况下，列应该只添加到几个表中，这些表我们不知道(我们对你的设计一无所知)，但最有可能在关系的最低点。然后，如果您需要知道BusinessUnit与您将确保您的查询JOIN到相关表，以找出行属于BusinessUnit(或者根据您的设计使用EXISTS)。

话虽这么说，您可以这样做，但它可能不会很快，而且代价高昂。它当然不会解决我上面提到的引用完整性问题。为此，您需要使用动态SQL。当我们更新450~个表时，我将每个UPDATE包装在它自己的事务中，这样当进程运行时，它不会最终锁定每一个你的桌子。我也假设您是在SQL Server的支持版本，因此可以访问STRING_AGG。如果您不使用旧的FOR XML PATH方法。

DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) = NCHAR(10);
SELECT @SQL = STRING_AGG(
N'BEGIN TRANSCATION;' + @CRLF +
N'    UPDATE ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + @CRLF +
N'    SET ' + QUOTENAME(c.[name]) + N' = 1;' + @CRLF +
N'COMMIT;',@CRLF)
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.[name] = N'BusinessUnit';
--Your best friends
--PRINT @SQL;
--SELECT @SQL;
EXEC sys.sp_executesql @SQL;

Answer 2

不完全是这样，您将不得不生成动态SQL或仅编写脚本。

我通常从INFORMATION_SCHEMA(或sys.tables)中选择表，然后将其复制到Excel中，并使用公式生成UPDATE语句(或其他)。复制到管理工作室&新闻。