Joe Webb Blog

Musing and observations about SQL Server, other technogies, and sometimes just life in general

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

Legacy Comments


Richard
2009-01-19
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.

Stephen S Sibert
2009-01-23
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.

Remote DBA
2009-02-17
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

omhoge
2009-02-23
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.

Paddy Power
2009-05-20
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.

Mike Anderson
2009-08-28
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!

Regeln von Slots
2010-03-22
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.

Joe Webb
2010-03-23
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.

mulberry alexa
2011-07-12
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.

Moncler On Sale
2011-09-05
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.

Chris Bag
2011-10-20
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!

mulberry outlet
2011-10-31
re: Adding a column to every table in a database
A wide range of Moncler Jackets hot sale now. If you are looking for

armani exchange watches
2011-11-08
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,

mulberry purse
2011-11-15
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.

KOKO
2012-04-02
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.

asicsliving
2012-04-02
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.

jordanretroshoesonline
2012-04-02
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!!

hotel in albuquerque
2012-07-20
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.