Automatically update particular data of a specific column with new data in all tables

Question

--TO UPDATE PARTICULAR COLUMN IN ALL TABLES IN A DATABASE
declare 
      @query nvarchar(max)='',
      @newValue varchar(10)='NEWVALUEHERE', 
      @oldValue varchar(10)='OLDVALUEHERE'

select 
    @query = @query + ' Update ' +
             quotename(object_name(c.object_id)) + 
             ' set [COLUMNNAMEHERE] = @newValue where [COLUMNNAMEHERE] = @oldValue ;'
from 
    sys.columns c 
join
    sys.all_objects o on c.object_id = o.object_id 
where 
    c.name = 'COLUMNNAMEHERE' and o.type = 'U'

select @query

--IF EVERYTHING SEEMS FINE COMMENT THE UPPER LINE AND UNCOMMENT THE LINE BELOW AND EXECUTE
--exec sp_executesql @query, N'@newValue varchar(10), @oldValue varchar(10)', @newValue=@newValue,@oldValue=@oldValue

So, I have this query that gets the job done, but after executing select @query I need to manually copy paste the result and replace the ; with \n go \n in ' Update ' + quotename(object_name(c.object_id)) + ' set [COLUMNNAMEHERE] = @newValue where [COLUMNNAMEHERE] = @oldValue ;' using regular expression option in Ctrl + H due to potential errors that could end the query execution prematurely.

Due to which the last line of my query

exec sp_executesql @query, N'@newValue varchar(10), @oldValue varchar(10)', @newValue=@newValue,@oldValue=@oldValue

is rendered useless and I am unable to make this query automatic.

Answer 1

If you must do this, just use CHAR(10) in your dynamic SQL:

declare 
      @query nvarchar(max)='',
      @newValue varchar(10)='NEWVALUEHERE', 
      @oldValue varchar(10)='OLDVALUEHERE'

select @query = @query + 'Update ' + quotename(object_name(c.object_id)) + ' set [COLUMNNAMEHERE] = @newValue where [COLUMNNAMEHERE] = @oldValue;' + CHAR(10) + 'GO' + CHAR(10)
from sys.columns c JOIN sys.all_objects o on c.object_id=o.object_id 
where c.name = 'COLUMNNAMEHERE' and o.type='U'

select @query