|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:
The result set is more conveniently handled as a user-defined function that returns a table:
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
Now create a query (view, stored procedure, UDF) that manufactures the statements needed to accomplish the task:
If you make a copy of the Northwind sample database called NorthwindTest
and run this code against it, the results look like this:
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.