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)

Answer 2

You can use this query I use the where clause in this query (it is optional) to find all tables that have ID and add the new field to them.

you can change where clause for example find all tables that have BusinessId column and add new filed or remove where clause and add for all tables

DECLARE @SQL varchar(max)

SELECT @SQL= STUFF((SELECT ';' + 'ALTER TABLE ' + t.TABLE_SCHEMA+'.' +t.TABLE_NAME+ ' ADD newfield nvarchar(max)' 

from information_schema.tables t
inner join information_schema.columns c on c.table_name = t.table_name
                                and c.table_schema = t.table_schema
where c.column_name = 'id'
      and t.table_schema not in ('information_schema', 'pg_catalog')
      and t.table_type = 'BASE TABLE'
      FOR XML PATH('')),1,1,'')


EXEC (@SQL)

Answer 3

This stored procedure works fine

USE databaseName;
exec sp_msforeachtable 'alter table ? Add [DateCreated] datetime not null default getdate()';

Answer 4

declare @i int

set @i=1
while(@i<45)
begin
    declare @sql varchar(200)

    with TempTable as (select Row_number() over(order by stdID) as RowNo,* from SomeTable)

    select @sql= 'alter table Table'+(select Name from TempTable where RowNo=@i)+' add NewColumn int'

    exec (@sql)
    set @i=@i+1
end

Answer 5

No, there is no single statement that will add a column to all the tables in your database.

Next time please tag your question with the RDBMS you're using. If there were a way, we wouldn't be able to give you the command without knowing which database system you are using.