Have you ever faced a situation where you needed to update a specific column in multiple tables in your SQL Server database? Recently, I received an interesting question from a reader who was looking for a solution to update the “ModifiedDate” column in their database. In this article, I will share two methods to accomplish this task.

Method 1: Using INFORMATION_SCHEMA

The first method involves using the INFORMATION_SCHEMA views to generate the necessary UPDATE statements. Here’s an example script:

SELECT 'UPDATE ' + T.TABLE_SCHEMA + '.' + T.TABLE_NAME + ' SET ModifiedDate = GETDATE() WHERE ModifiedDate IS NULL;'
FROM INFORMATION_SCHEMA.TABLES T
INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON T.TABLE_NAME = C.TABLE_NAME AND C.COLUMN_NAME = 'ModifiedDate'
WHERE T.TABLE_TYPE = 'BASE TABLE'
ORDER BY T.TABLE_SCHEMA, T.TABLE_NAME;

This script will generate a series of UPDATE statements for each table in your database that has a “ModifiedDate” column. It will update the column with the current date and time only if the value is NULL.

Method 2: Using DMV

The second method involves using the Dynamic Management Views (DMV) to achieve the same result. Here’s an example script:

SELECT 'UPDATE ' + SCHEMA_NAME(t.schema_id) + '.' + t.name + ' SET ModifiedDate = GETDATE() WHERE ModifiedDate IS NULL;'
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name = 'ModifiedDate'
ORDER BY SCHEMA_NAME(t.schema_id), t.name;

Similar to the first method, this script will generate the necessary UPDATE statements for each table that has a “ModifiedDate” column. It will update the column with the current date and time only if the value is NULL.

Both methods provide a convenient way to update specific columns in multiple tables without affecting the rows that already have valid values. You can modify these scripts to update other columns or retrieve different data as per your requirements.

I hope you find these methods helpful in your SQL Server database management tasks. If you have any further questions or need assistance, feel free to reach out to me.