Alter all tables in a database


Here is a code sample how to add or drop a ccolumn from all tables within a database.
 
–EXEC sp_MSForEachTable ‘ IF exists (select 1 from Information_Schema.Columns  WHERE Table_Name = PARSENAME(”?”,1) AND Column_Name = ”Col1”)
–ALTER TABLE ?  DROP COLUMN Col1′
 
EXEC sp_MSForEachTable ‘ IF not exists (select 1 from Information_Schema.Columns  WHERE Table_Name = PARSENAME(”?”,1) and Column_Name = ”Col1”)
ALTER TABLE ?  ADD Col1 nvarchar(50) null’
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s