So I needed to add 2 columns to about 20 tables.  Not much and I could have easily wrote a script for each table.  But thats boring.  So I started searching around.

I came up with this

Select 'Alter table '+table_name+' Add yourcol datatype' from information_schema.tables
where table_name<>'dtProperties'

Cool but I wanted it to run and execute at once, not just create the scripts.  The only way I could think was is with a cursor.  Something I didn't want to do.  But did anyway.  I came up with this.

DECLARE Alter_tables_cursor CURSOR
   FOR
   select table_name from information_schema.tables where table_name<>'dtProperties' and table_type<>'VIEW'
OPEN Alter_tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM Alter_tables_cursor INTO @tablename
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
 PRINT 'Alter table '+@tablename+' Add ModifiedBy int, ModifiedOn datetime'
 EXEC('Alter table '+@tablename+' Add ModifiedBy int, ModifiedOn datetime')
 FETCH NEXT FROM Alter_tables_cursor INTO @tablename
 
END
PRINT 'All user-defined tables have been Altered.'
DEALLOCATE Alter_tables_cursor
I'm sure there are better ways.  If so post them.