Comprehensive Database Search and Replace

Posted: 12/19/2007 10:18:00 PM
This bit of SQL will search all the tables in your database for the occurrence of a string and replace it with another string
The other day I ran into a situation where I wanted to run a database wide search/replace. Similar to a text search/replace, except I wanted to update every VARCHAR field in every table. I didn't want to manually run updates on every table and field, so I used the INFORMATION_SCHEMA to find all the VARCHAR columns, then built the SQL Statement dynamically.

There are two lines in the script, the first one merely finds occurrences of the string without updating it. The second one actually performs the update. The first line allows you to see if the string occurs and then go investigate to see if you really want to replace it first. Running a script like this against a database can be very dangerous, use it at your own risk!


declare @sql varchar(1000)
declare @column_name varchar(50)
declare @table_name varchar(50)
declare @replacetext varchar(50)
declare @withtext varchar(50)

select @replacetext = 'text to replace'
select @withtext = 'the new text'

DECLARE columns_cursor CURSOR FOR
select table_name, column_name from information_schema.columns
where data_type = 'varchar' and table_name in
(select table_name from information_schema.tables
where table_type = 'BASE TABLE')

OPEN columns_cursor
FETCH NEXT FROM columns_cursor INTO @table_name, @column_name
WHILE @@FETCH_STATUS = 0
BEGIN
--see if there are any replacements needed by table and field
select @sql = 'select ''' + @table_name + ''' as tablename,
''' + @column_name + ''' as columnname, count(*) from '
+ @table_name
+ ' where ' + @column_name + ' like ''%' + @replacetext
+ '%'' having count(*) > 0 '
--perform the replacements
--select @sql = 'update ' + @table_name + ' set ' + @column_name
--+ '=replace(' + @column_name + ',@replacetext,@withtext) where '
--+ @column_name + ' like ''%' + @replacetext + '%'''
print @sql
exec(@sql)
FETCH NEXT FROM columns_cursor INTO @table_name, @column_name
END
CLOSE columns_cursor
DEALLOCATE columns_cursor


I know some people really loathe cursors, but they have their place. If you have a better way to do a database wide search/replace without them, let me know.

Of course this script could be put into a stored procedure and you could pass in a list of tables to update or get as fancy as you want.

I've been considering adding this functionality to Qrimp, to let users do an application-wide search/replace similar to the application-wide search that is available now. I think it might be so easy to make a mistake with it that I am reluctant to add the feature.

Is an application-wide search/replace something we should add to all Qrimp Apps?