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.

About these ads

9 Responses to Adding a Column to Every Table

  1. Thanks for that. I came across the same problem for our servers but now use a product to do the modifications – http://www.red-gate.com/products/SQL_Compare/index.htm

    It’s expensive considering you can just use alter scripts like you have above, but the software enables our developers to do the synchronisations rather than having the DB Admin’s time taken up in a simple table update. Good piece of kit!

    NB – I am in no way affiliated with Redgate, just think they have a good product :)

  2. Joe says:

    I actually used this script to do the initial ALTER to the development environment.

    I love Red-Gate SQL Compare and use it quite a lot when moving code from dev to test to production. It’s great.

    Thanks for mentioning it.

  3. Pingback: Weekly Link Post 138 « Rhonda Tipton’s WebLog

  4. jcdyntek says:

    Interesting use of an undocumented proc, how about a simple select statement from an information schema view.

    USE AdventureWorks;
    select ‘Alter table [‘+table_schema+’].[‘+table_name+’] ADD Date_Created DATETIME DEFAULT GETUTCDATE();’ sqlscript
    from INFORMATION_SCHEMA.TABLES

  5. Joe says:

    Hi Jcdyntek –

    Yes, I really like the INFORMATION_SCHEMAs. Thanks for sharing the script.

    Joe

  6. Pingback: Log Buffer #184, a Carnival of the Vanities for DBAs | The Pythian Blog

  7. perumalraj says:

    excellent information. i want more about the database administration.

  8. Pingback: A NULL Is Not Unknown « WebbTech Solutions

  9. I tend to agree with every thing that was put into writing
    inside “Adding a Column to Every Table « WebbTech Solutions”.
    Thanks for all the actual details.I appreciate it,Beatriz

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

Follow

Get every new post delivered to your Inbox.

Join 36 other followers