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.

possible duplicate of http://stackoverflow.com/questions/1779743/adding-a-column-to-all-user-tables-in-t-sql — JAiro, Feb 28 '11 at 18:38
@JAiro: That is definitely a relevant link, but the "if not exists" rule is important and makes it a little more complex. — Scott Stafford, Feb 28 '11 at 18:41
ok so you could complete the information with this : http://stackoverflow.com/questions/133031/how-to-check-if-column-exists-in-sql-server-table — JAiro, Feb 28 '11 at 18:44
Your root cause of the problem is relying on the INFORMATION_SCHEMA view and having to split the name into schema and object. Just use `object_id(''?'')` and you'll be fine. An even faster check is `if COLUMNPROPERTY(object_id(''?''), ''CreatedOn'', ''ColumnId'') is null`. — Remus Rusanu, Feb 28 '11 at 18:56

score 33 · Accepted Answer · answered Feb 28 '11 at 18:50

33

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

answered Feb 28 '11 at 18:50

Remus Rusanu

288,378
40
442
569

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). – Phil Helmer Mar 01 '11 at 07:01
1

@PhilHelmer This along with an example would've made a great answer. – xr280xr Jul 28 '17 at 14:33

score 5 · Answer 2 · answered Feb 28 '11 at 18:44

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
'

@Remus: Yes, if OP is using schemas, then this becomes an issue. +1 to your answer. — Joe Stefanelli, Feb 28 '11 at 18:56

score 0 · Answer 3 · edited Oct 04 '19 at 14:54

0

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

edited Oct 04 '19 at 14:54

alexherm

1,362
2
18
31

answered Oct 04 '19 at 14:09

Ajeet Verma

1,021
1
7
25

consider adding explanations to your answer. Maybe remove the screenshot of code too... – Jean-François Fabre Oct 04 '19 at 22:11
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. – Ajeet Verma Oct 05 '19 at 06:00

score -1 · Answer 4 · answered Feb 28 '11 at 18:45

-1

Maybe like this:

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
'

?

Or even like this:

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
'

answered Feb 28 '11 at 18:45

Andriy M

76,112
17
94
154

2

-1 obviously haven't tested this before posting. `[?]` adds bracket around around an already bracketed name, resulting in `[[schemaname].[tablename]]` which is incorrect. – Remus Rusanu Feb 28 '11 at 18:52
@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). – Andriy M Feb 28 '11 at 19:32
...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. – Remus Rusanu Feb 28 '11 at 20:05
@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. :) – Andriy M Feb 28 '11 at 20:16
Oh, sry, then I misinterpreted your comments. The fact that `sp_help [?]` works the way it is at least mystifying for me as well. – Remus Rusanu Feb 28 '11 at 20:59
I run this exception `Cannot find the object "[dbo].[table1]" because it does not exist or you do not have permissions.` – Bellash Feb 15 '19 at 08:55
@Bellash: That's right. The correct solution is shown [elsewhere](https://stackoverflow.com/a/5146229/297408). – Andriy M Feb 15 '19 at 14:33

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

4 Answers4

Linked