Search code examples
sqlsql-serversql-server-2012ssms-2012

how to do a search and replace for a string in mssql 2012

I am having to find and replace a substring over all columns in all tables in a given database.

I tried this code from sqlserver 2012 ssms but resulting in errors from http://www.dbtalks.com/uploadfile/anjudidi/find-and-replace-string-values-in-all-tables-and-column-in-s/ Find and Replace string Values in All Tables and column in SQL Serve

I think its for older version, it having problems with some of the tables names that start with a number: example dbo.123myTable

Appreciate all the help in advance

Error Print:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '.153'. UPDATE dbo.153Test2dev SET [ALCDescription] = REPLACE(convert(nvarchar(max),[ALCDescription]),'TestsMT','Glan') WHERE [ALCDescription] LIKE '%SherlinMT%' Updated: 1

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '.153'. UPDATE dbo.153TypeTest2 SET [FormTypeDescription] = REPLACE(convert(nvarchar(max),[FormTypeDescription]),'TestsMT','Glan') WHERE [FormTypeDescription] LIKE '%SherlinMT%' Updated: 1

Solution

  • Just as a guess, to add delimiters to your table names, modify the script you linked to by editing this line:

        SET @sqlCommand = 'UPDATE ' + @schema + '.' + @table + ' SET [' + @columnName + '] = REPLACE(convert(nvarchar(max),[' + @columnName + ']),''' + @stringToFind + ''',''' + @stringToReplace + ''')'

    and change it to 

      SET @sqlCommand = 'UPDATE [' + @schema + '].[' + @table + '] SET [' + @columnName + '] = REPLACE(convert(nvarchar(max),[' + @columnName + ']),''' + @stringToFind + ''',''' + @stringToReplace + ''')'