SQL - 将具有默认值的新列添加到DB中的所有表

I have the following statement:

我有以下声明：

EXEC sp_MSforeachtable '
    DECLARE @defaultId integer;
    SET @defaultId = 13;

    IF EXISTS(SELECT name FROM sys.sysobjects WHERE Name = N''?'' AND xtype = N''U'')
    BEGIN   
        IF COL_LENGTH(''[?]'',''NewColumn'') IS NULL
        BEGIN
            ALTER TABLE [?] ADD NewColumn integer null
            UPDATE [?]
            SET NewColumn = @defaultId
        END
    END
'

I'm trying to add NewColumn column to all the tables in my DB that don't have it and set the default value to 13. I execute it and it says it completed successfully, but nothing has changed. What am I doing wrong here?

我正在尝试将NewColumn列添加到我的数据库中没有它的所有表中，并将默认值设置为13.我执行它并表示它已成功完成，但没有任何更改。我在这做错了什么？

Note: I got a table called User hence the "[]". I don't know if this adds extra complexity to it.

注意：我有一个名为User的表，因此是“[]”。我不知道这是否会增加额外的复杂性。

3 个解决方案

#1

I don't particulary like debugging sp_MSforeachtable code so I suggest you this method:

我不像调试sp_MSforeachach代码那样特别所以我建议你这个方法：

DECLARE @Name AS VARCHAR(128)

DECLARE C_Table CURSOR FOR
    SELECT sys.objects.Name
    FROM sys.objects
    WHERE sys.objects.type = 'U' AND
        sys.objects.Name NOT IN (
            SELECT DISTINCT sys.objects.Name
            FROM sys.objects INNER JOIN
            sys.columns ON sys.objects.object_id = sys.columns.object_id 
            WHERE sys.objects.type = 'U' AND
                sys.columns.Name = 'NewColumn'
        )

OPEN C_Table
FETCH NEXT FROM C_Table INTO @Name

WHILE @@FETCH_STATUS = 0
BEGIN

    EXEC('ALTER TABLE ' + @Name + ' ADD NewColumn INTEGER NULL')
    EXEC('ALTER TABLE ' + @Name + ' ADD CONSTRAINT DF_' + @Name + '_A DEFAULT 13 FOR NewColumn')
    EXEC('UPDATE ' + @Name + ' SET NewColumn = 13')

    FETCH NEXT FROM C_Table INTO @Name

END

CLOSE C_Table
DEALLOCATE C_Table

This is an alternative solution using sp_MSforeachtable and your syntax; please note that:

这是使用sp_MSforeachtable和您的语法的替代解决方案;请注意：

you need to PARSENAME the name provided by sp_MSforeachtable (i.e. [yourschema].[yourtable]) because sys.objects.name contains only table name (i.e.: yourtable)
你需要PARSENAME由sp_MSforeachtable提供的名称（即[yourschema]。[yourtable]）因为sys.objects.name只包含表名（即：yourtable）
you have to use 2 step because it seems that is not possible to use GO inside a sp_MSforeachtable
你必须使用2步，因为似乎无法在sp_MSforeachtable中使用GO
you can't use variable in ALTER TABLE but you must specify a fixed DEFAULT
您不能在ALTER TABLE中使用变量，但必须指定固定的DEFAULT

This is the code:

这是代码：

EXEC sp_MSforeachtable '

    IF EXISTS(SELECT * FROM sys.sysobjects WHERE Name = PARSENAME(''?'', 1) AND xtype = N''U'')
    BEGIN   
        IF COL_LENGTH(PARSENAME(''?'', 1), ''NewColumn'') IS NULL
        BEGIN
            ALTER TABLE ? ADD NewColumn INT NULL DEFAULT(13)
        END
    END

'

EXEC sp_MSforeachtable '

    DECLARE @defaultId INT
    SELECT @defaultId = 13

    IF EXISTS(SELECT * FROM sys.sysobjects WHERE Name = PARSENAME(''?'', 1) AND xtype = N''U'')
    BEGIN   
        IF NOT COL_LENGTH(''?'', ''NewColumn'') IS NULL
        BEGIN
            UPDATE ? SET NewColumn = @defaultId
        END
    END

'

#2

Most likely that NewColumn is not recognized. try

很可能NewColumn不被认可。尝试

EXEC sp_MSforeachtable '
DECLARE @defaultId integer;
SET @defaultId = 13;
IF EXISTS(SELECT name FROM sys.sysobjects WHERE Name = PARSENAME(''?'',1) AND xtype = N''U'')
BEGIN   
    IF COL_LENGTH(''?'',''NewColumn'') IS NULL
    BEGIN
        ALTER TABLE ? ADD NewColumn integer null
        EXEC(CONCAT(''UPDATE ? SET NewColumn = '', @defaultId))
    END
END'

#3

You need to do this in two steps.

您需要分两步完成此操作。

The update statement is evaluated before execution and will throw an error (as there is no column at this moment)

在执行之前评估update语句并将抛出错误（因为此时没有列）

Step 1 (alter table):

第1步（更改表）：

EXEC sp_MSforeachtable '

IF EXISTS(SELECT * FROM sys.sysobjects WHERE Name = parsename(N''?'', 1) AND xtype = N''U'')
BEGIN   
    IF COL_LENGTH(''[?]'',''NewColumn'') IS NULL
    BEGIN
        ALTER TABLE [?] ADD NewColumn integer null
    END
END
'

Step 2 (set value):

第2步（设定值）：

EXEC sp_MSforeachtable '
DECLARE @defaultId integer;
SET @defaultId = 13;

IF EXISTS (SELECT * FROM sys.sysobjects WHERE Name = parsename(N''?'', 1) AND xtype = N''U'')
BEGIN   
    IF COL_LENGTH(''[?]'',''NewColumn'') IS not NULL
    BEGIN
        UPDATE [?]
        SET NewColumn = @defaultId
    END
END
'

You mentioned a default value. You only updated the values of the new column with a fixed value without defining a default. This might be an difference although not important for this issue.

你提到了一个默认值。您只使用固定值更新了新列的值，而没有定义默认值。这可能是一个区别，虽然对这个问题不重要。

#1