Search and Replace SQL Server Data Across Tables

November 6, 2002

People often wonder how they can search and replace data stored in their tables. While this doesn't sound like a very good thing to do, there are some genuine situations where such functionality is needed. For example, spelling mistakes in the column values come to mind, as do moving a database from one location to another or wanting to replace all location-specific content.

Is there a built-in 'Global Search and Replace' functionality available in SQL Server? No. The REPLACE function provided by T-SQL can replace a given string in a string variable or a column, but you cannot directly use the REPLACE function to loop through all character columns of all tables to globally replace a string. This needs some programming effort.

Narayana Vyas Kondreddi offers a stored procedure named SearchAndReplace that searches through all the character columns of all tables in the current database and replaces the given string with another user-provided string. It accepts a search string and a replace string as input parameters; goes and searches all char, varchar, nchar, nvarchar columns of all tables (only user-created tables -- system tables are excluded) owned by all users in the current database and replaces all occurences of the search string with the replace string.