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.

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