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 INTSET @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 1OPEN TAB_CURSOR
FETCH NEXT FROM TAB_CURSOR
INTO @schema,
@table,
@object_idWHILE @@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 @columnNameWHILE @@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 ‘—————————————————-‘
ENDFETCH NEXT FROM COL_CURSOR
INTO @columnName
ENDCLOSE COL_CURSOR
DEALLOCATE COL_CURSORFETCH NEXT FROM TAB_CURSOR
INTO @schema,
@table,
@object_id
ENDCLOSE 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
—————————————————-