Is it possible to Add column to multiple table simultaneously?

Question

I am using SQL Server. I want to add a single column named [DateCreated] to multiple tables. Is it possible that with a single statement I could add this column to all the tables in my database?

I stumble upon an answer by Joe Steffaneli in which he suggested a query which in turn returns rows consisting Alter table statements. Query is as follows :

select 'alter table ' + quotename(s.name) + '.' + quotename(t.name) + ' add [DateModified] datetime'
    from sys.columns c
        inner join sys.tables t
            on c.object_id = t.object_id
        inner join sys.schemas s
            on t.schema_id = s.schema_id
        left join sys.columns c2
            on t.object_id = c2.object_id
                and c2.name = 'DateModified'
    where c.name = 'DateCreated'
        and t.type = 'U'
        and c2.column_id is null /* DateModified column does not already exist */ 

Is there any way that I can execute returned rows? Sorry for English.

Answer 1

You probably need something like this. Check that the script does what you want before running it (adds a non null column with a default value of getdate())!

DECLARE @Dynsql nvarchar(max) 
SET @Dynsql = ''

SELECT @Dynsql = @Dynsql + '
alter table ' + QUOTENAME(SCHEMA_NAME(schema_id))+ '.' + QUOTENAME(name)  + 
' add [DateCreated] datetime not null default getdate()' 
FROM sys.tables
WHERE type='U' and object_id NOT IN (select object_id from sys.columns where name='DateCreated')


EXEC (@Dynsql)