在所有表中，如果不存在则添加一列？

Question

我使用SQL Server 2005/2008。如果表不存在，我需要在表中添加一列。这将适用于给定数据库中的所有表。我希望我的解决方案是正确的，但出现了问题。

如何完成这个操作？

这是我的代码：

EXEC sp_MSforeachtable '
    declare @tblname varchar(255);
    SET @tblname =  PARSENAME("?",1);

    if not exists (select column_name from INFORMATION_SCHEMA.columns 
                   where table_name = @tblname and column_name = ''CreatedOn'') 
    begin
        ALTER TABLE @tblname ADD CreatedOn datetime NOT NULL DEFAULT getdate();
    end
'

但是我收到错误信息：

错误102：在 '@tblname' 附近语法不正确。 在 'CreatedOn' 附近语法不正确。 在 '@tblname' 附近语法不正确。 在 'CreatedOn' 附近语法不正确。 ...... 对于每个表格都是这样。

- Scott Stafford

---

可能是 https://dev59.com/8HI-5IYBdhLWcg3wlpaW 的重复问题。 - JAiro

@JAiro：那绝对是一个相关的链接，但“如果不存在”的规则很重要，使它变得更加复杂。 - Scott Stafford

好的，你可以用这个来补充信息: https://dev59.com/XHVC5IYBdhLWcg3w-WOs - JAiro

你的问题根本原因在于依赖 INFORMATION_SCHEMA 视图并需要将名称拆分为模式和对象。只需使用 object_id(''?'') 即可解决问题。甚至更快的检查方法是 if COLUMNPROPERTY(object_id(''?''), ''CreatedOn'', ''ColumnId'') is null。 - Remus Rusanu

Answer 1

在DDL中无法使用像@tableName这样的变量。此外，将名称分成部分并忽略模式只会导致错误。您应该在SQL批处理参数中仅使用''?''替换，并依赖于MSforeachtable替换：

EXEC sp_MSforeachtable '
if not exists (select * from sys.columns 
               where object_id = object_id(''?'')
               and name = ''CreatedOn'') 
begin
    ALTER TABLE ? ADD CreatedOn datetime NOT NULL DEFAULT getdate();
end';

- Remus Rusanu

---

请注意，sp_msforeachtable是不受支持的存储过程，因此不应用于生产代码。虽然它会增加几行代码，但如果您使用从sys.schemas和sys.tables选择定义的游标，则正在使用T-SQL的文档部分，您可以通过仅更改WHERE表达式来影响所有或某些表，并且性能相同。此外，如果您使用quotename函数，则需要处理的名称限定符较少。最后，您可以更灵活地使用模式/表名称（例如，用于日志）。 - Phil Helmer

@PhilHelmer 这个回答如果再加上一个例子就更好了。 - xr280xr

Answer 2

您需要混合一些动态SQL。以下是该代码：

EXEC sp_MSforeachtable '
    declare @tblname varchar(255);
    SET @tblname =  PARSENAME("?",1);
    declare @sql nvarchar(1000);

    if not exists (select column_name from INFORMATION_SCHEMA.columns 
                   where table_name = @tblname and column_name = ''CreatedOn'') 
    begin
        set @sql = N''ALTER TABLE '' +  @tblname + N'' ADD CreatedOn datetime NOT NULL DEFAULT getdate();''
        exec sp_executesql @sql
    end
'

- Joe Stefanelli

---

@Remus：是的，如果OP正在使用模式，则会出现此问题。对你的回答加一。 - Joe Stefanelli

Answer 3

DECLARE @Column VARCHAR(100) = 'Inserted_date'
DECLARE @sql VARCHAR(max) = NULL

SELECT @sql += ' ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + 'ADD' + @Column + 'datetime NOT NULL DEFAULT getdate()' + '; '
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
    AND TABLE_NAME IN (
        SELECT DISTINCT NAME
        FROM SYS.TABLES
        WHERE type = 'U'
            AND Object_id IN (
                SELECT DISTINCT Object_id
                FROM SYS.COLUMNS
                WHERE NAME != @Column
                )
        )
EXEC Sp_executesql @sql

- Ajeet Verma

---

请考虑在您的答案中添加解释。也许可以删除代码截图... - Jean-François Fabre

嗨，Jean，实际上我只是将列名存储在一个变量中，并找出该特定列不存在的表名，然后将修改命令保存在另一个变量@sql中，我们运行该命令以执行添加操作。 - Ajeet Verma

Answer 4

可能是这样的：

EXEC sp_MSforeachtable '
    declare @tblname varchar(255);
    SET @tblname =  PARSENAME("?",1);

    if not exists (select column_name from INFORMATION_SCHEMA.columns 
                   where table_name = @tblname and column_name = ''CreatedOn'') 
    begin
        ALTER TABLE [?] ADD CreatedOn datetime NOT NULL DEFAULT getdate();
    end
'

?

甚至可以像这样：

EXEC sp_MSforeachtable '
    if not exists (select column_name from INFORMATION_SCHEMA.columns 
                   where table_name = ''?'' and column_name = ''CreatedOn'') 
    begin
        ALTER TABLE [?] ADD CreatedOn datetime NOT NULL DEFAULT getdate();
    end
'

- Andriy M

---

-1 显然在发布之前没有进行测试。[?]会在已括号起来的名称周围添加方括号，导致结果为[[schemaname].[tablename]]这是不正确的。 - Remus Rusanu

@Remus Rusanu: 我测试了这个命令：sp_MSforeachtable 'EXEC sp_help ?; EXEC sp_columns ?'。但是它报错了。然后我把它改成了 sp_MSforeachtable 'EXEC sp_help [?]; EXEC sp_columns [?]'，这样就可以正常运行了（在SQL Server 2008 R2上）。 - Andriy M

然而你没有测试 ALTER TABLE [?]。sp_help 和 sp_columns 都是存储过程，而 ? 的替换不会起作用，因为 sp_help [foo].[bar] 是无效的语法。但是 sp_help [[foo]].[bar]]] 是正确的语法，由于参数的处理方式，它实际上可以正常工作。然而， ALTER TABLE [[foo]].[bar]]] 不起作用。简而言之：您发布的代码未通过基本语法检查，任何人都可以验证这一点。 - Remus Rusanu

@Remus Rusanu：我并不是在争论。我发布我的测试例子有两个原因：一是提供我产生错觉的原因，二是可能有人能够解释为什么我会产生这种错觉（除了我无知的明显原因）。所以，最终，谢谢。 :) - Andriy M

哦，抱歉，那我误解了你的评论。sp_help [?] 的工作方式至少对我来说也是神秘的。 - Remus Rusanu