SQL Server Find and Replace Values in All Tables and All Text Columns

SQL Server Find and Replace Values in All Tables and All Text Columns

• @stringToFind
• @stringToReplace

• SET NOCOUNT ON

  DECLARE @stringToFind VARCHAR(100)
  DECLARE @stringToReplace VARCHAR(100)
  DECLARE @schema sysname
  DECLARE @table sysname
  DECLARE @count INT
  DECLARE @sqlCommand VARCHAR(8000)
  DECLARE @where VARCHAR(8000)
  DECLARE @columnName sysname
  DECLARE @object_id INT

  SET @stringToFind = ‘Smith’
  SET @stringToReplace = ‘Jones’

  DECLARE TAB_CURSOR CURSOR FOR
  SELECT B.NAME AS SCHEMANAME,
  A.NAME AS TABLENAME,
  A.OBJECT_ID
  FROM sys.objects A
  INNER JOIN sys.schemas B
  ON A.SCHEMA_ID = B.SCHEMA_ID
  WHERE TYPE = ‘U’
  ORDER BY 1

  OPEN TAB_CURSOR

  FETCH NEXT FROM TAB_CURSOR
  INTO @schema,
  @table,
  @object_id

  WHILE @@FETCH_STATUS = 0
  BEGIN
  DECLARE COL_CURSOR CURSOR FOR
  SELECT A.NAME
  FROM sys.columns A
  INNER JOIN sys.types B
  ON A.SYSTEM_TYPE_ID = B.SYSTEM_TYPE_ID
  WHERE OBJECT_ID = @object_id
  AND IS_COMPUTED = 0
  AND B.NAME IN (‘char’,’nchar’,’nvarchar’,’varchar’,’text’,’ntext’)

  OPEN COL_CURSOR

  FETCH NEXT FROM COL_CURSOR
  INTO @columnName

  WHILE @@FETCH_STATUS = 0
  BEGIN
  SET @sqlCommand = ‘UPDATE ‘ + @schema + ‘.’ + @table + ‘ SET [‘ + @columnName + ‘] =
  REPLACE(convert(nvarchar(max),[‘ + @columnName + ‘]),”’ +
  @stringToFind + ”’,”’ + @stringToReplace + ”’)’

  SET @where = ‘ WHERE [‘ + @columnName + ‘] LIKE ”%’ + @stringToFind + ‘%”’

  EXEC( @sqlCommand + @where)

  SET @count = @@ROWCOUNT

  IF @count > 0
  BEGIN
  PRINT @sqlCommand + @where
  PRINT ‘Updated: ‘ + CONVERT(VARCHAR(10),@count)
  PRINT ‘—————————————————-‘
  END

  FETCH NEXT FROM COL_CURSOR
  INTO @columnName
  END

  CLOSE COL_CURSOR
  DEALLOCATE COL_CURSOR

  FETCH NEXT FROM TAB_CURSOR
  INTO @schema,
  @table,
  @object_id
  END

  CLOSE TAB_CURSOR
  DEALLOCATE TAB_CURSOR

If the above is run in the AdventureWorks database as is, these are the messages that are returned.

• UPDATE Person.Address SET [AddressLine1] = REPLACE(convert(nvarchar(max),[AddressLine1]),’Smith’,’Jones’) WHERE [AddressLine1] LIKE ‘%Smith%’
  Updated: 2
  —————————————————-
  UPDATE Person.Address SET [City] = REPLACE(convert(nvarchar(max),[City]),’Smith’,’Jones’) WHERE [City] LIKE ‘%Smith%’
  Updated: 1
  —————————————————-
  UPDATE Person.Contact SET [LastName] = REPLACE(convert(nvarchar(max),[LastName]),’Smith’,’Jones’) WHERE [LastName] LIKE ‘%Smith%’
  Updated: 105
  —————————————————-
  UPDATE Production.ProductReview SET [ReviewerName] = REPLACE(convert(nvarchar(max),[ReviewerName]),’Smith’,’Jones’) WHERE [ReviewerName] LIKE ‘%Smith%’
  Updated: 1
  —————————————————-