Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Adding a Column to Every Table

Robot_2010_03_24.jpg

I am Script. I will update your database.

Despite the best laid plans, sometimes circumstances or project scope change and the definition of one or more tables in your database must be altered after they have been created. It’s just a fact of life for a DBA.

Change Happens

Frequently these changes affect more than one database in your environment. The development, test, and eventually even the production databases must have their definitions updated to reflect the newly implemented changes.

For cases like this, I generally prefer to script out the necessary changes rather than using the Management Studio tools. Scripting reduces the likelihood of an unintentionally errant change while updating the 100+ tables in the database. If one database or table is done correctly, you can be assured that the rest are in order as well. That’s not to say you shouldn’t test; you should. But the changes will be much more consistent when they are implemented via a script rather than by hand.

Scripting ALTER TABLE Statements

Recently I was tasked with adding a column to every user table in a database. There were lots of tables in this database and doing it by hand was not a good alternative. Of course if the changes were successful in Dev, they would need to propagated to Test and Production as well. So, I used T-SQL to dynamically create a script for me.

There a number of ways to accomplish this. A cursor or a while loop come to mind. However, since I knew that the new column name did not exist in any of the tables, I could use a shortcut and make use of the sp_msforeachtable system stored procedure. I described its use in a prior blog.

The first step is to use the stored procedure to generate T-SQL code to implement the change. For this example, we want to add a datetime column called Date_Created to each table in the AdventureWorks database. In a new query window, type the following code.

USE AdventureWorks;
EXEC sp_msforeachtable
‘PRINT ”ALTER TABLE ? ADD Date_Created DATETIME DEFAULT GETUTCDATE();”’ ;

When this script is executed, the Messages pane of the query window is filled with a list of DDL (Data Definition Language) statements to implement our change. The output is shown in the following figure.

foreachtable_2010_03_24.jpg

After reviewing these DDL statement to ensure they are indeed what we wish to do, we can then copy and paste them into a query window for execution.

Of course this implementation doesn’t have error handling. If a column by that name already exists, the DDL statement will fail for that table. If you are unsure whether a column already exists, you should use one of the other implementations (cursor, while, etc) so you can check for the presence of the column before issuing the ALTER TABLE statement.

I hope you find this useful. If you have any favorite scripting techniques or comments on this one, please feel free to share in the comments section below.


Comments

Posted by Dukagjin Maloku on 24 March 2010

Once again the power of SP_MSForEeachTable as main solution for 100+ tables!

Posted by Anonymous on 24 March 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, Adding a Column to Every Table - Joe Webb         [sqlservercentral.com]        on Topsy.com

Posted by Dukagjin Maloku on 25 March 2010

I forgot to ask you here if I add column with T-SQL, it will created at the end of the table, so how can we creat it at the beginning table, if I need it, for example I want to be an identity column and I want to add it but as first column in that table!?

Posted by Glenn Berry on 25 March 2010

Nice solution, Joe.  

Posted by Anonymous on 20 May 2010

Pingback from  Jeep Patriot Episode Floor Mats, Patriot Williamsburg Holiday Inn

Posted by Anonymous on 22 May 2010

Pingback from  Ford Freestar Dash Lights, E 250 Econoline Freestar Exhaust Manifold Auto Parts

Posted by Anonymous on 23 May 2010

Pingback from  Gl 10 Taillight Sealed Beam Subaru Forester, Forester Xt Review 2010 Subaru Outback - 3.binggreen.com

Posted by Anonymous on 24 May 2010

Pingback from  British Stealth Technology, 1997 Dodge Stealth Rt - 43.tgrconversions.com

Posted by Anonymous on 25 May 2010

Pingback from  500sec Hose, 500sec Part Online Spark Plug Wires - 137.jeepsunlimted.com

Posted by Anonymous on 25 May 2010

Pingback from  850csi Auto Parts Used Bmw Alpina B7, 850csi Code Bmw - 355.eumreborn.com

Posted by Anonymous on 25 May 2010

Pingback from  1991 Grand Marquis With Carriage Roof, Grand Marquis 4x4 - 90.tijuanareader.com

Posted by Anonymous on 27 May 2010

Pingback from  1999 - 2008 @ Nespresso D150, D150 Part Ram 1500 Ramcharger Dodge Durango - 484.tvshowzone.com

Posted by Anonymous on 27 May 2010

Pingback from  1989 - 2000 @ Cl500 Bulb Amg Taillight, Cls55 Amg Headlight Cl500 - 321.myipgirl.com

Leave a Comment

Please register or log in to leave a comment.