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.

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

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com

10 thoughts on “SQL SERVER – Script to Update a Specific Column in Entire Database

  1. 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.

    Like

  2. 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;

    Like

  3. 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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s