How to update specific column in entire database using SQL

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:

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.

8b10820deb updatescriptgen


Discover more from mycodetips

Subscribe to get the latest posts sent to your email.

Discover more from mycodetips

Subscribe now to keep reading and get access to the full archive.

Continue reading

Scroll to Top