Joe Webb

Musing and observations about SQL Server, other technogies, and sometimes just life in general
posts - 85, comments - 857, trackbacks - 0

My Links

News

This blog has moved!

Click here for the new location.

Follow me on Twitter
Add to Technorati Favorites

Search this Blog
 




Archives

Post Categories

About me

Adding a column to every table in a 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.

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.

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 GETDATE();''' ;

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.

AlterTable-2008-01-15

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.

Cheers!

Joe

kick it on DotNetKicks.com

Print | posted on Thursday, January 15, 2009 8:59 AM | Filed Under [ T-SQL ]

Feedback

Gravatar

# re: Adding a column to every table in a database

Just a thought - GetDate should never be used in a database, either explicitly or as a default. Even if your application only ever deals with one time-zone, you can still end up with ambiguous values when the clocks go back.
1/19/2009 7:35 AM | Richard
Gravatar

# re: Adding a column to every table in a database

Thanks for your article. While I do not specifically need to do this right now, I sincerely appreciate your approach to the problem. That helps me very much. Thanks again.
1/23/2009 9:07 AM | Stephen S Sibert
Gravatar

# re: Adding a column to every table in a database

To Richard:
You are completely right but in some cases database needs to deal with the time. I new one database a part of order processing system that was so tightly dependent on the time that if you run a stored procedure within 1 second 2 times you will see completely different effects. but there was no other choise except deal with application every time you need date, which whould slow down system in 1000 times
2/17/2009 8:27 AM | Remote DBA
Gravatar

# re: Adding a column to every table in a database

Great Idea, thank you, sp_msforeachtable looks very handy!

Unfortunately trying this in SQL 2005 studio, nothing prints besides "Command(s) completed successfully." in the messages window.
I think you may need to insert the table name into a temp table then use a second select statement to get all the actual ALTER TABLE commands printed out.
2/23/2009 12:20 PM | omhoge
Gravatar

# re: Adding a column to every table in a database

The way you've dealt with this issue is exemplary. I appreciate the fact that you've shared it so other people can benefit.
5/20/2009 6:22 PM | Paddy Power
Gravatar

# re: Adding a column to every table in a database

Bravo in the simplicity of such a powerful example. You saved me a bunch of time going forward in any development with this handy manipulation. Go eat some Oreos!
8/28/2009 10:53 AM | Mike Anderson
Gravatar

# re: Adding a column to every table in a database

Thanks for such a beautifully composed, informative article.I think your designing work to this is really great .I really appreciate your work to this site.So thanks for it.I hope you can continue this type of hard work to this site in future also..Because this blog is really very informative and it helps me lot.
3/22/2010 12:23 AM | Regeln von Slots
Gravatar

# re: Adding a column to every table in a database

Thanks Kevin & everyone for the kind words. I'm glad you've found this info useful.

By the way, I've moved my blog to a new location - http://www.webbtechsolutions.com/blog.
3/23/2010 7:41 AM | Joe Webb
Gravatar

# re: Adding a column to every table in a database

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,
10/16/2010 3:59 PM | columbia jackets
Gravatar

# re: Adding a column to every table in a database

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.

snow boots | snow boots for women | columbia sportswear | columbia sportswear outlet | cheap north face jackets | the north face outlet | mac makeup | cheap makeup
10/19/2010 4:33 AM | furry boots
Gravatar

# re: Adding a column to every table in a database

Colgate-Palmolive - trusted brands for dental care, personal care, home care and pet nutrition. Find the right Colgate toothpaste, toothbrush and other Colgate University website home page. Colgate is one of the leading liberal arts universities in the country. Colgate Dec 21, 2010 Worldwide leader and manufacturer of snowboards, boots, bindings, outerwear, goggles and protection. Join the Burton Community of riders.The House Boardshop. Save 60% on Snowboards Snowboard Boots Bindings Clothing Jackets and Pants. Ships within 24 hours. Free Shipping. 1-800-992-7245. snowboard Introducing the World's First FN 15 Gauge Angled Cordless Finish Nailer. Introducing the World's First Cordless 28° Wire-weld Framing Nailer. Framing Nailers · 21° Plastic Collated Round Head Metal Connector Nails · 33 Roofing. Nailers & Staplers · Bostitch Caps & Staples BOSTITCH Europe bostitch nailer Darna's East Angel Harbor Hat Shoppe is dedicated to designing for your millinery delight a Victorian hat, Edwardian tea hat or a Flapper hat and Tea Hats at Maggie Mae Designs Millinery features ladies' tea hats for special occasion. We specialize in fancy tea hats, dressy hats, ladies' church hats, tea hat Odors are also called scents, which can refer to both pleasant and unpleasant The terms fragrance, scent, and aroma are used primarily by the food and effluvia from a substance that affect the sense of smell: as a : an odor left by an animal on a surface passed over b : a characteristic or particular odor; scent
12/24/2010 11:21 PM | nrerr
Gravatar

# re: Adding a column to every table in a database

Somehow, quality has a way of giving a special feeling of superiority in everything you say and do. Having an air of confidence is not the only reason to purchase a real designer mulberry bags, though.Individuality.Each woman is different in the way they carry themselves, hairstyle, clothing and makeup.
7/12/2011 8:06 PM | mulberry alexa
Gravatar

# re: Adding a column to every table in a database

Let moncler down jacket company with you in this winter. A wide range of Moncler Jackets hot sale now. If you are looking for a excellent Moncler Store online, we can offer what you need. Different moncler jackets both for men and women. The Moncler Jacket Men featureing cool feeling.
9/5/2011 2:07 AM | Moncler On Sale
Gravatar

# re: Adding a column to every table in a database

I had to do the same thing a few weeks back! Took a while to get my head around it, wish I had found this article before - would have saved quite a bit of time!
10/20/2011 1:19 PM | Chris Bag
Gravatar

# re: Adding a column to every table in a database

A wide range of Moncler Jackets hot sale now. If you are looking for
10/31/2011 9:16 PM | mulberry outlet
Gravatar

# UGG Kensington

<br>The autumn collection of UGG Kensington are now in stock . The range features Kensington boots.
<br>UGG Kensington Boots catalog has boots, sneakers and slippers are for everyone that wants to keep warn and dry with the ultimate style. UGGs Kensington are the hot boots right now yet there are lots more Ugg cozy feet products to choose from. The winter season selection has an exiting choice in a number of colors.

Kensington UGG Boots
Kensington UGGs are fashionable biker type boots that are amazingly comfy. Totally Kensington UGG, 100 % cool. They really are warm too because the insole is authentic sheepskin and so are the lined uppers. The external soles are a particular EVA with synthetic rubber pod inserts, so no chance of slipping and they last forever too.

11/1/2011 2:13 AM | UGG Kensington
Gravatar

# The explanation for this process adjust is usually that the 1980's are rear, higher not to mention wonderful


Nowhere is that this a good deal more manifest versus his / her most current wrist watch types which can be preparing a new times of all of the fashion periodicals.Those Armani Young women writst watch line when actually extraordinary artwork that're in the forefront, often the AR5750 a lot of women armani exchange watches play contains a chequer extended artwork in a large diamond that includes Armani's trademark emblem graven around. Theme song is traditional elegance in a sensible price range for &single lb;150 bodyweight. All of the ladies' series chain can also have often the exceptional AR5710 that also includes an absolute flower your antique watches switch in the stainless-steel situation.
Every single and every apparel paper is normally lending both ladies and men model guidance on how to bear off the very 1980's try looking in 12 months 2010. That eighties era revitalization means probably the most widespread look at products with this stage,
11/8/2011 7:04 PM | armani exchange watches
Gravatar

# re: Adding a column to every table in a database


I like your theme, did you buy it or is it original? I’m in search of something similar to this for use on my web site
I don’t think these are really the best pieces of advice. Some things you should leave to professionals is surgery and dentistry.
11/15/2011 2:10 AM | mulberry purse
Gravatar

# re: Adding a column to every table in a database

shure headphones collection currently launched the new SE115m+ sound isolating headset that is a definitely upgrade to Shure SM115 headset, what’ s more, Shure 115m+ is completely compatible with iPhone and iPod models, like iPhone4, iPhone 4S, iPod Touch, iPod Nano, etc.
11/17/2011 2:46 AM | Dean Paul
Gravatar

# re: Adding a column to every table in a database

Good work, I enjoyed A brief message to the person who jacked up my car at 3 o'clock in the morning, removed the lug nuts from the passenger-side wheel, took the wheel (presumably to replace a damaged wheel on his own Scion TC), and left my 3-wheeled car for me to discover at 8:20 a.m. this morning – jotsheet. It was good. View my blog sometime, it all about Winter Springs, FL Real Estate.
4/2/2012 12:58 AM | KOKO
Gravatar

# re: Adding a column to every table in a database

Nice post. I study one thing more challenging on totally different blogs everyday. It's going to always be stimulating to learn content from other writers and observe just a little something from their store. I'd desire to use some with the content material on my weblog whether you don't mind. Natually I'll offer you a link on your internet blog. Thanks for sharing.
4/2/2012 1:04 AM | asicsliving
Gravatar

# re: Adding a column to every table in a database

Hi, i think that i saw you visited my website thus i came to "return the favor".I'm trying to find things to enhance my site!I suppose its ok to use a few of your ideas!!
4/2/2012 1:08 AM | jordanretroshoesonline
Gravatar

# re: Adding a column to every table in a database

Just thought the date will be automatically sync from the database according to time zone, and supposed that there will be no use of GetDate.
7/20/2012 4:42 AM | hotel in albuquerque
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET