March 6, 2017 at 2:29 am
I have this code below that replaces all Test1 strings with Test2 strings in all the tables in a given DB.
SET NOCOUNT ON
DECLARE @stringToFind VARCHAR(100)
DECLARE @stringReplacement 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 = 'Test1'
SET @stringReplacement = 'Test2'
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
Question: How can I do this such that I don't need to run that script for every string replacement?
For example, I should be able to input multiple string_to_find-replacement_string pair values.
Test1 -> Test2
Test3 -> Test4
Test5 -> Test6
and so on.
--
March 6, 2017 at 2:51 am
You could change your UPDATE statement so that you have lots of nested REPLACEs. The drawback with that is that you'll need to change the code if you need to do more replacements than it supports. (For example, it currently supports one; you could change it so it supports three, but then you'd need to change it again if you needed to do four.) The other alternative is to have a loop within a loop so that each REPLACE is done separately on each column. That wouldn't perform as well, and would chew up much more transaction log, but it would be more flexible.
John
March 6, 2017 at 3:00 am
If the loop within the loop is done, should the string pairs be contained in array or tempdb?
March 6, 2017 at 3:08 am
We don't really have arrays in SQL Server. I think your choices are to declare individual scalar variables (@ReplaceThis1, @ReplaceWith1, @ReplaceThis2, @ReplaceWith2, ...) but that also suffers from having to be changed when you want to do more replacements. So you're probably better off creating a temp table or declaring a table variable with columns ReplaceThis and ReplaceWith. That's totally scalable. And you could also do nested REPLACEs with that, but the code to generate the code would rapidly start looking very ugly!
John
March 6, 2017 at 3:17 am
Thanks John for your generous suggestions. The script that I have been working on will be used for migration of @email1.com to @email2.com of about 200 to 300 accounts.
March 6, 2017 at 3:59 am
Thanks again John. It worked!
SET NOCOUNT ON
DECLARE @stringToFind VARCHAR(100)
DECLARE @stringReplacement 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
declare @Emails table
(
PK int IDENTITY(1,1),
DatabaseID int,
Original varchar(100),
Replacement varchar(100)
)
-- insert a bunch rows into @databases
INSERT INTO @Emails (DatabaseID, Original, Replacement) SELECT 1,'origemail1.com', 'origemail2.com'
INSERT INTO @Emails (DatabaseID, Original, Replacement) SELECT 1,'origemail3.com, ''origemail4.com'
Declare @maxPK int;Select @maxPK = MAX(PK) From @Emails
Declare @pk int;Set @pk = 1
While @pk <= @maxPK
Begin
/* Get one record (you can read the values into some variables) */
Select /* DatabaseID, */ @stringToFind = Original, @stringReplacement = Replacement
From @Emails
Where PK = @pk
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 + ''',''' + @stringReplacement + ''')'
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
Select @pk = @pk + 1
End
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply