I need to update 2 columns in all tables in a database where these columns are blank or NULL. These 2 columns are CreatedBy and CreatedOn where CreatedBy = ‘System’ and CreatedOn = GETDATE(). I can obtain the SCHEMA and TABLE name where these columns are present with the following query:
select c.TABLE_SCHEMA, c.TABLE_NAME
from information_schema.columns c
INNER JOIN information_schema.TABLES t ON c.TABLE_NAME = t.TABLE_NAME
WHERE t.TABLE_TYPE = 'BASE TABLE' AND COLUMN_NAME = 'CreatedOn'
How do I loop through this list and update the columns with the default values? I suspect using a CTE with the list of SCHEMAs and TABLEs is a good starting place.