Can you really search and replace all occurrences of the string in most posts in most tables of the database? I personally use Microsoft SQL Server.
Not easily, though I'm able to factor of two ways to get it done:
- Write a number of saved methods that identify all varchar and text posts of tables, and generate individual update claims for every column of every table from the form "UPDATE foo SET BAR = REPLACE(BAR,'foobar','quux')". This can most likely involve lots of queries from the system tables, with many different experimentation -- Microsoft does not walk out its method to document these items.
- Export the whole database to some single text file, perform a search/replace on that, after which re-import the whole database. Considering that you are using MS SQL Server, this really is really the simpler approach. Microsoft produced the Microsoft SQL Server Database Publishing Wizard for some other reasons, however it constitutes a fine tool for conveying all the tables of the SQL Server database like a text file that contains pure SQL DDL and DML. Run the tool to export all the tables for any database, edit the resulting file since you need, after which feed the file to
sqlcmdto recreate the database.
Given an option, I'd make use of the second method, as lengthy because the DPW works together with your version of SQL Server. The final time I made use of the tool, it met me (MS SQL Server 2000 / 2005) however it had some eccentricities whenever using database Roles.
A great beginning point for writing this type of totally the "Search all columns in all the tables in a database for a specific value" saved procedure. The entire code reaches the hyperlink (not trivial, but copy/paste it and employ it, it simply works).
After that on it's relatively trivial to amend the code to perform a replace from the found values.