SQL SERVER – Script to Update a Specific Column in Entire Database

Last week, I have received a very interesting question and I find in email and I really liked the question as I had to play around with SQL Script for a while to come up with the answer he was looking for. Please read the question and I believe that all of us face this kind of situation.

“Pinal,

In our database we have recently introduced ModifiedDate column in all of the tables. Now onwards any update happens in the row, we are updating current date and time to that field.

Now here is the issue, when we added that field we did not update it with a default value because we were not sure when we will go live with the system so we let it be NULL. Now modification to the application went live yesterday and we are now updating this field.

Here is where I need your help. We need to update all the tables in our database where we have column created ModifiedDate and now want to update with current datetime. As our system is already live since yesterday there are several thousands of the rows which are already updated with real world value so we do not want to update those values. Essentially, in our entire database where ever there is a ModifiedDate column and if it is NULL we want to update that with current date time? 

Do you have a script for it?”

Honestly I did not have such a script. This is very specific required but I was able to come up with two different methods how he can use this method.

Method 1 : 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;

Method 2: 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.

SQL SERVER - Script to Update a Specific Column in Entire Database updatescriptgen

We can pretty much the update script to any other SELECT statement and retrieve any other data as well.

Reference: Pinal Dave (https://blog.sqlauthority.com) 

SQL Joins
Previous Post
SQLAuthority News – Advantages of Distance Learning
Next Post
SQL SERVER – QUOTED_IDENTIFIER ON/OFF Explanation and Example – Question on Real World Usage

Related Posts

11 Comments. Leave new

  • shivendra Kumar Yadav
    June 4, 2013 11:29 am

    This is nice one to handle this kind of situation. If my developers come with this kind of scenario, I just add the following informal text with this result set in continuation what you did and tell them to follow this step:

    1. Add this string in our “SELECT” clause

    2. Change the result mode to “Text”.

    3. And If we are not getting the full result set than increase the per column size though options.

    This will give the well formatted and easy to implement dynamic code. If error will come for any specific table than we can handle it separately.

    Reply
  • shivendrayadav
    June 4, 2013 1:21 pm

    The String is : + CHAR(10) + ‘PRINT ”’ + T.TABLE_NAME + ‘ has updated”’ + CHAR(10) + ‘GO’

    Reply
  • Why cant we use
    sp_msforeachtable ‘UPDATE ? SET ModifiedDate = GETDATE() WHERE ModifiedDate IS NULL’

    Reply
  • @Biju Thomas because the script will fail as it is not necessary that all tables in database has ModifiedDate Column

    Reply
  • Naveen Chowdary
    July 18, 2013 3:53 pm

    Hi, What Is The Soluton for this

    TITLE: Microsoft SQL Server Management Studio

    Reply
  • Very good again pinal Dave very useful! Thanks

    Reply
  • if the Column Name is Created By i have 10 data for created by how u will do in one single shot

    Reply
  • vijaykumar kagne
    February 7, 2014 3:11 pm

    sir i want update data into table for multiple column but is there any simple way to update data into table but not mentioning all columns name and values?

    ex:
    i have table of 100 column s

    update tablename
    set
    column 1=value,
    column 2=value,
    . //is there any other way to simplified to enter data into table
    .
    .
    column 100=value,
    where id=some value;

    Reply
  • Hello Mr Pinal
    In our company we are changing old cost centres with new cost centres.So i am supposed to come up with a SQL script that will be able to change from old cost centres TO NEW cost centres in 900+ rows
    I am using SQL server 2008 r2 management studio!please help

    Reply
  • very useful! Thanks pinal Dave

    Reply

Leave a Reply