Loop through all tables in DB and add column

[ErrolDC2]

Could someone share a simple exmaple with me?

Thanks,

 

[k01]

You would like to add new column on all tables of DB?




koichi

http://aboutabi.blogspot.com/

 

[ErrolDC2]

Yes. All the tables in this database are identical. I need to add 2 columns to the tables to upgrade them for higher revision of the application that is using the database.

 

[gmmastros]

[!]** I strongly encourage you to make a good backup of the database before doing this.[/!]

sp_msforeachtable 'Alter Table ? Add [!]NewColumn Int[/!]'

You will need to modify the above statement to accommodate your column name and data type. If the column already exists, you will get an error message like this...

[tt][red]Column names in each table must be unique. Column name 'NewColumn' in table 'dbo.testData' is specified more than once.[/red][/tt]

So, if the column already exists, it will not be added, but the statement will continue on to the next table.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom