Add a column if it doesn't exist to all tables?

Question

I'm using SQL Server 2005/2008. I need to add a column to a table if it does not yet exist. This will apply to all tables in a given database. I hoped I was close, but I'm having issues with this solution.

How can this be done?

Here's what I have:

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
'

But I get errors:

Error 102: Incorrect syntax near '@tblname'. Incorrect syntax near 'CreatedOn'. Incorrect syntax near '@tblname'. Incorrect syntax near 'CreatedOn'. ... and so on, for each table.

Solution 1

You cannot use variables, like @tableName, in DDL. Besides, splinting the name into part and ignoring the schema can only result in bugs. You should just use the ''?'' replacement in the SQL batch parameter and rely on the MSforeachtable replacement:

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

Solution 2

You'll need to mix in a bit of dynamic SQL. This should work:

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
'

Comments

[Remus Rusanu]

-1 obviously haven't tested this before posting. [?] adds bracket around around an already bracketed name, resulting in [[schemaname].[tablename]] which is incorrect.

[Joe Stefanelli]

@Remus: Yes, if OP is using schemas, then this becomes an issue. +1 to your answer.

[Andriy M]

@Remus Rusanu: I tested this: sp_MSforeachtable 'EXEC sp_help ?; EXEC sp_columns ?'. That gave a syntax error. I then changed it to sp_MSforeachtable 'EXEC sp_help [?]; EXEC sp_columns [?]', and it worked (SQL Server 2008 R2).

[Remus Rusanu]

...yet you did not test the OP ALTER TABLE [?]. Both sp_help and sp_columns are stored procedures, and the ? replacement is not going to work because sp_help [foo].[bar] is invalid syntax. But sp_help [[foo]].[bar]]] is correct syntax and due to how parameters are handled, it actually ends up working. ALTER TABLE [[foo]].[bar]]] though does not work. Too keep a long story short: the code you posted does not pass basic syntax checks, and this can be verified by anybody.

[Andriy M]

@Remus Rusanu: And I'm not arguing. I posted my test example for two reasons: to provide the cause of my delusion and to possibly have someone explain me why I was deluded (beside the obvious reason of my being ignorant). So, in the end, thanks. :)

[Remus Rusanu]

Oh, sry, then I misinterpreted your comments. The fact that sp_help [?] works the way it is at least mystifying for me as well.

[Phil Helmer]

Keep in mind that sp_msforeachtable is an unsupported stored procedure, so it should not be used for production code. Although it adds a few lines of code, if you use a cursor defined as a select from sys.schemas and sys.tables, you are using a documented part of T-SQL, you have the option of affecting all or some of the tables by merely changing a WHERE expression, and the performance is the same. Also, you have fewer name qualifiers to deal with if you use the quotename function. Lastly, you have more flexibility in using the schema/table names however you wish (i.e. for a log).

[xr280xr]

@PhilHelmer This along with an example would've made a great answer.

[Bellash]

I run this exception Cannot find the object "[dbo].[table1]" because it does not exist or you do not have permissions.

[Andriy M]

@Bellash: That's right. The correct solution is shown elsewhere.

[Jean-François Fabre]

consider adding explanations to your answer. Maybe remove the screenshot of code too...

[Ajeet Verma]

Hi Jean,Actually I'm just storing the column name in a variable and find out the table name in which that particular column doesn't exist and then save the alter command in another variable @sql which we run for execution for adding.