如果所有表中不存在某列，是否添加该列？

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”附近的语法不正确。 ...对于每个表，依此类推。

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';

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
'

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

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
'