Alter All Tables Script

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 found up with this from this post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65351

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.

http://www.jiltedcitizen.com