SQL Server Alter Evey Table

Alter every table in a database

Notice to subscribers: Due to the holiday, the SQL Server newsletter will not be delivered on Tuesday, Dec. 28, 2004. Look for your next edition of SQL Server on Tuesday, Jan. 4, 2005.

All developers make mistakes from time to time; sometimes this happens because we fail to build in obvious but unstated requirements.

Here's an example: Your database is up and running successfully but various errors in data entry and updating mandate a new requirement: add two columns (LastUpdated and UpdatedBy) to every table. There are hundreds of tables, so it's impractical to perform this task by hand.

This is clearly a chunk of reusable code, so you want to write it once and ensure that it can work on every database. (You might have to refine it slightly for each new database by, for example, changing the column names. But the idea is, you want a procedure to walk all the tables in a database and add one or more columns.)

It's easy to obtain the list of user tables:

SELECT Name FROM sysobjects WHERE Type = 'U' ORDER BY Name

The result set is more conveniently handled as a user-defined function that returns a table:

CREATE FUNCTION dbo.UserTables_fnt
()
RETURNS TABLE
AS
RETURN
(SELECT TOP 100 PERCENT name
FROM dbo.sysobjects
WHERE type = 'U')
ORDER BY name
)

Suppose that you want to add a column called LastUpdated (of type TimeStamp) to every table in the database. To add such a column to any given table, e.g., Customers, your command would look like this:

ALTER TABLE MyDB.dbo.Customers ADD LastUpdated TimeStamp NULL

Now you create a query (view, stored procedure, UDF) that manufactures the statements you need to accomplish your task:

SELECT
'ALTER TABLE NorthwindTest.dbo.[' + name + '] ADD LastUpdated TimeStamp NULL'
AS CommandText
FROM dbo.UserTables_fnt()

Assuming that you make a copy of the Northwind sample database called NorthwindTest and run this code against it, the results look like this:

ALTER TABLE NorthwindTest.dbo.[Categories] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.[CustomerCustomerDemo] ADD LastUpdated TimeStamp
NULL
ALTER TABLE NorthwindTest.dbo.[CustomerDemographics] ADD LastUpdated TimeStamp
NULL
ALTER TABLE NorthwindTest.dbo.[Customers] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.[dtproperties] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.[Employees] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.[EmployeeTerritories] ADD LastUpdated TimeStamp
NULL
ALTER TABLE NorthwindTest.dbo.[Order Details] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.[Orders] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.[Products] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.[Region] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.[Shippers] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.[Suppliers] ADD LastUpdated TimeStamp NULL
ALTER TABLE NorthwindTest.dbo.[Territories] ADD LastUpdated TimeStamp NULL

I used brackets around the table names because they guard against a problematic table name: Order Details. In the absence of spaces, the parser doesn't care about the brackets; but in the presence of spaces, the generated SQL will cause an error.

You can deal with this result set in a variety of ways, including paste it into Query Analyzer and execute it, turn it into a stored procedure, or turn it into an updateable view. Given its one-off nature, I prefer the first choice.

I love writing code that writes code because then I don't have to do it--and it never misspells anything. You can extend this concept to perform just about any DML action that you could perform by hand.

If you're going to try this technique, I strongly encourage you to create a SELECT query first, which manufactures the desired DML, so you can inspect it and check its syntax before running it.

Arthur Fuller has been developing database applications for more than 20 years. He frequently works with Access ADPs, Microsoft SQL 2000, MySQL, and .NET.