Добавить столбец, если он не существует для всех таблиц?

Я использую 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'. ... и т.д. для каждой таблицы.

Ответ 1

Вы не можете использовать переменные, такие как @tableName, в DDL. Кроме того, разбиение имени на часть и игнорирование схемы может привести к ошибкам. Вы должны просто использовать замену ''? '' В параметре пакета 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';

Ответ 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
'

Ответ 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

Ответ 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
'