Easily update all tables that have the same field name

We talked about concatenating multiple records into a single string using FOR XML PATH and we also talked about EXISTS clause. Here’s a practical example that incorporates both.

In one of our projects, we have an archive database that contains historical data of select tables from the production database. Since the archive database may have multiple entries of the same record from different times, we do have to maintain a date stamp and the history tables have their own keys. The archive database gets data imported into it at a specified time. As the last step of the import process, we want to ensure that all new entries added to the archive database have the same date stamp, in our case we use a field called HistDate. It stores the date the record was archived.

Here’s the hard way 

You could create a stored procedure with a T-SQL update statement for each table that will update the HistDate field in each table. The drawback to this approach would be having to maintain it every time you add another table to your archive solution.

Now the easy way

To do this, we have to use dynamic SQL. But we don’t want to get errors due to tables not having the column named HistDate — not all tables in the archive database have/need this column. Therefore, to build the update statements only for the tables that has it, we need to look up the sys.columns and determine whether the table contains a column with that name.

Here’s the T-SQL:
DECLARE @parms nvarchar(MAX) = '@HistDate datetime',
@sql nvarchar(MAX) = (
SELECT
'UPDATE dbo.' + QUOTENAME(o.name) + ' SET HistDate = @HistDate WHERE HistDate IS NULL;'
FROM sys.objects AS o
WHERE o.type = 'U'
AND o.schema_id = SCHEMA_ID('dbo')
AND EXISTS (
SELECT NULL
FROM sys.columns AS c
WHERE c.object_id = o.object_id
AND c.name = 'HistDate'
)
FOR XML PATH('')
);
EXEC sys.sp_executesql @sql, @parms, @HistDate;

Note the use of parameters which is very helpful in managing the inputs such as the @HistDate variable and protecting from SQL injection.

To use the code, simply create a procedure that accepts the HistDate parameter and add it as another line in your archive job. Alternatively, you can hire us to create a great archive solution for your database! Contact us here.

What other nifty solutions have you come up with?