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.
Reference: Pinal Dave (https://blog.sqlauthority.com)
11 Comments. Leave new
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.
The String is : + CHAR(10) + ‘PRINT ”’ + T.TABLE_NAME + ‘ has updated”’ + CHAR(10) + ‘GO’
Why cant we use
sp_msforeachtable ‘UPDATE ? SET ModifiedDate = GETDATE() WHERE ModifiedDate IS NULL’
@Biju Thomas because the script will fail as it is not necessary that all tables in database has ModifiedDate Column
Yes i am agree with your point . PINAL DAVE please explain what should we do..
Hi, What Is The Soluton for this
TITLE: Microsoft SQL Server Management Studio
Very good again pinal Dave very useful! Thanks
if the Column Name is Created By i have 10 data for created by how u will do in one single shot
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;
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
very useful! Thanks pinal Dave