I have the same column in multiple tables, and want to update that column in all tables to a specific value. How can I do this?

Question

The column is "CreatedDateTime" which is pretty self-explanatory. It tracks whatever time the record was commited. I need to update this value in over 100 tables and would rather have a cool SQL trick to do it in a couple lines rather than copy pasting 100 lines with the only difference being the table name.

Any help would be appreciated, having a hard time finding anything on updating columns across tables (which is weird and probably bad practice anyways, and I'm sorry for that).

Thanks!

EDIT: This post showed me how to get all the tables that have the column

I want to show all tables that have specified column name

if that's any help. It's a start for me anyways.

Do you have conditions on which rows in the tables identified have to be updated with new "CreatedDateTime", or is the update for ALL rows in all Tables that have the "CreatedDateTime" column? — Shiva, Nov 05 '13 at 19:33

score 14 · Accepted Answer · answered Nov 05 '13 at 19:45

14

If that's a one time task, just run this query, copy & paste the result to query window and run it

Select 'UPDATE ' + TABLE_NAME + ' SET CreatedDateTime = ''<<New Value>>'' '
From INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME = 'CreatedDateTime'

answered Nov 05 '13 at 19:45

EricZ

6,065
1
30
30

Clever, short and sweet. – Basil Bourque Nov 05 '13 at 19:51
This made my day! Thanks. – Diogo Almeida Feb 09 '21 at 12:13

score 5 · Answer 2 · edited Sep 11 '17 at 14:23

You could try using a cursor : like this

declare cur cursor for Select Table_Name From INFORMATION_SCHEMA.COLUMNS Where column_name = 'CreatedDateTime'
declare @tablename nvarchar(max)
declare @sqlstring nvarchar(max)

open cur
    fetch next from cur into @tablename
    while @@fetch_status=0
    begin
        --print @tablename

        set @sqlstring = 'update ' + @tablename + ' set CreatedDateTime = getdate()'

        exec sp_executesql @sqlstring

        fetch next from cur into @tablename
    end

close cur
deallocate cur

score 3 · Answer 3 · answered Nov 05 '13 at 19:46

3

You can use the Information_Schema.Columns to build update scripts for you.

    Declare @ColName as nVarchar(100), @NewValue as nVarchar(50) 
    Set @ColName = 'Modified'  -- 'your col name'
    Set @NewValue = '2013-11-04 15:22:31' -- your date time value
    Select 'Update ' + TABLE_NAME + ' set ' + COLUMN_NAME + ' = ''' + @NewValue + '''' From INFORMATION_SCHEMA.COLUMNS Where column_name = 'modified'

answered Nov 05 '13 at 19:46

bsivel

2,821
5
25
32

A minute too late for the checkmark, if I had 15 rep I'd at least give you an upvote... Thanks anyways though! – Gage Nov 05 '13 at 19:53

I have the same column in multiple tables, and want to update that column in all tables to a specific value. How can I do this?

3 Answers3

Linked