Recently I created about 60 tables in a single database, but realized I forgot a column when building the schemas. Instead of manually entering the
ALTER TABLE statement for each and every table, I found the following SQL snippet to generate the statement for each table:
SELECT 'ALTER TABLE ' + QUOTENAME(ss.name) + '.' + QUOTENAME(st.name) + ' ADD [<New Column Name>] <Column Type> NULL;' FROM sys.tables st INNER JOIN sys.schemas ss on st.[schema_id] = ss.[schema_id] WHERE st.is_ms_shipped = 0 AND NOT EXISTS ( SELECT 1 FROM sys.columns sc WHERE sc.[object_id] = st.[object_id] AND sc.name = 'report_date' );
Then it's simply a matter of copying and pasting the results into a new query and executing!