update field inside table

Hi, i have one table with some fields that were hacked and all ntext and varchar fields were added some text inside. I need to update all rows and fields that have this text to take out the text and leave the rest inside.

lets say that the table name is mytable and I have a lots of fields, so i need some query to get the fields name automatic and then go over all rows and check if the field have the text lets say "hacked text", then i need to take out only the part hacked text inside the fields
LVL 1
rafaelrglAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
UPDATE mytable
SET 
your_column1 = REPLACE(your_column1, 'hacked text', '') , 
your_column2 = REPLACE(your_column2, 'hacked text', '') 
repeat_for_each_column = REPLACE(repeat_for_each_column, 'hacked text', '') 

Open in new window

This assumes that 'hacked text' wasn't in there in the first place.  If it was, no way around that one, unless the hacked text is always the prefix/suffix/anything you can define..
0
Kyle AbrahamsSenior .Net DeveloperCommented:
This will generate Jim's sample with individual updates.  

Run this query, copy the result set into management studio and let fly.
select 'update ' + cast(table_catalog as varchar(255)) + '.' 
+ cast(table_schema as varchar(25)) + '.'
+ cast(table_name as varchar(255)) + ' set [' +
cast(column_name as varchar(255)) + '] = replace([' + cast(column_name as varchar(255)) +
'],''this is your hacked text'', ''''); '
from INFORMATION_SCHEMA.COLUMNS
where DATA_TYPE in('varchar','nvarchar')

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.