Add columns to all tables in a database

from the Artful SQL Server & Access Tips List

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 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 you want to add a column called LastUpdated, of type TimeStamp, to every table in the database. To add it to Customers, you write like this: 
ALTER TABLE MyDB.dbo.Customers ADD LastUpdated TimeStamp NULL
Now create a query (view, stored procedure, UDF) that manufactures the statements needed to accomplish the task: 
SELECT 'ALTER TABLE NorthwindTest.dbo.[' + name + '] 
ADD LastUpdated TimeStamp NULL' AS CommandText
FROM dbo.UserTables_fnt()
If 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
Brackets around object names prevent errors caused by spaces in object names You can deal with this result set in a variety of ways: 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.

Last updated 22 May 2024
Return to the Artful SQL Server & Access Tips page