I have the same column in multiple tables, and wan

问题:

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.

回答1:

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'

回答2:

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

回答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'