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
    —————————————————-