How to update specific column in entire database using SQL
The UPDATE statement changes existing data in one or more rows in a table. The following illustrates the syntax of the UPDATE statement:
|
1 2 3 4 5 6 7 |
UPDATE table SET column1 = new_value1, column2 = new_value2, ... WHERE condition; |
Using INFORMATION_SCHEMA
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;
Using DMV
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;
Above scripts will create an UPDATE script which will do the task which is asked.
