Every now and then I come across some bad practices that needs repairing. This time the “no-no” was in a database tables. I had to change hard-coded links in quite a few tables. Replacing 10,000 absolute URLs spread across the database can be a huge headache, so I decided to work smart, not hard.
So I’ve started to work on a search & replace sproc using cursors (yes, I know…):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | DECLARE @SEARCH VARCHAR(1000) SET @SEARCH = '-=search string=-' DECLARE _cursor_ CURSOR LOCAL FAST_FORWARD FOR SELECT ID, TEXTPTR(-=the filed=-), CHARINDEX(@SEARCH,-=the filed=- )-1 FROM -=the TABLE=- WHRE -=the filed=- LIKE '%' + @SEARCH +'%' DECLARE @REPLACE VARCHAR(1000) SET @REPLACE = '-=replace string=-' DECLARE @length INT SET @lenght = LEN(@REPLACE) DECLARE @ptr BINARY(16) DECLARE @pos INT DECLARE @id INT OPEN _cursor_ FETCH NEXT FROM _cursor_ INTO @id, @ptr, @pos WHILE @@FETCH_STATUS = 0 BEGIN UPDATETEXT -=the TABLE=-.-=the field=- @ptr @pos @txtlen @ntxt FETCH NEXT FROM _cursor_ INTO @id, @ptr, @pos END CLOSE _cursor_ DEALLOCATE _cursor_ |
The initial query was working like a charm with the field data type being ntext; the only downside was if the string to replace was more than one time present in the field I had to run the query again and again. When I had to use it on another table with fields still on ntext, I’ve stumbled upon a strange error:
A cursor plan could not be generated for the given statement because
the textptr() function was used on a LOB column from one of the
base tables.
The LOB column is a Large Object Column, so after a little research I wrote another query that fixed the issue and did not have the downside of the first one:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | CREATE PROCEDURE FindReplace ( @TABLE VARCHAR(20), @Field VARCHAR(20), @WHERE VARCHAR(100), @Find VARCHAR(30), @REPLACE VARCHAR(30) ) AS DECLARE @query VARCHAR(8000) SET @query = 'UPDATE ' + @TABLE + ' SET ' + @Field + '= REPLACE(CONVERT(varchar(8000),' + @Field + '),''' + @Find + ''',''' + @REPLACE +''')' IF(@WHERE <> '') SET @query = @query + ' WHERE '+@WHERE EXECUTE (@query) GO -- usage: EXEC FindReplace 'table','field','where_clause','find','replace' |
The only downside of this approach is that it doesn’t work on fields larger than 8000 chraracters.
I hope this will be helpful to someone. The code is open for improvement.
Awesome script. It took me some time to find something like this. I tried updating it through ColdFusion and it worked, but it wasn’t very flexible. Know to little about sql scripting to give you any suggestions, sorry for that.
Cheers,
JP
Excellent script. Works like a champ, thanks
excellent mid blowing out standing awesome i cant say more than this
Was just curious if there was a way to check a WHOLE database instead of just one table for this script. Im not SQL oriented enough to edit the script myself so..
Great Script. Finally something that works!
ty so much for sharing this.