Query to find and replace a specific text in SQL Database (All tables and All Columns)

Question

I have a DB with +100 tables, most tables have a column that saves a URL but with different "Column Name" and URL is different as well. e.g.

In one table its

usa3030.Development.com/portal/mybook/chapter1/page1/line1

and in another one its

usa3030.Development.com/portal/mybook/chapter1

and so on, but for sure each URL have "usa3030.Development.com/portal/mybook/chapter1" in them

So I want a query that Will find each column that have text within column data (with "*" I think we call wildcard)

"usa3030.Development.com/portal/mybook/chapter1"

and replace it with

usa3030.Development.com/MyWorld/Chp1

Answer

Search Or replace Stored Procedure

AnandPhadke · Accepted Answer · 2012-08-15 09:49:54Z

1

Here I guess you have to go through the following steps:

DECLARE @string varchar(max)
For each table in sys.Objects (where type='U')

      For each column of the above table

        IF EXISTS(select * from tablename where colname like '%'+@string+'%')
        update table tablename SET colnmae=REPLACE(colname,'old','new') where colname like '%'+@string+'%')

answered Aug 15, 2012 at 9:49

AnandPhadke

13.5k5 gold badges28 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query to find and replace a specific text in SQL Database (All tables and All Columns)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related