Update sql code with new tables fields


I need to update old sql code(change schema,table,fields etc) with new schema ,table and fields  . I can do manually for few but have almost 150 of them and I don't want to do it manually. For example below code
SELECT  TOP 100 P.ProductID,
FROM Sales.SalesOrderDetail SOD
INNER JOIN Production.Product P
 ON SOD.ProductID = P.ProductID
WHERE SOD.UnitPrice > 1000

needs to be changed to below code(I have changed table,fields etc )

SELECT  TOP 100 P.ProdId,
FROM SalesRpt.SalesOrdDtl SOD
INNER JOIN Prod.Product P
 ON SOD.ProdId = P.ProdId
WHERE SOD.UntPrice > 1000

how to approach this scenario? hmm i can use excel and do replace but that works for only single files..any idea?

Thanks in advance.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Bill PrewIT / Software Engineering ConsultantCommented:
You might be able to use a search and replace utility ( I can recommend a few if we get that far) to automate the changes, but you will have to judge if that is is possible, I don't know the full context.

It looks like you have both table name and column name changes, as follows from your post:

Sales            => SalesRpt
Production       => Prod

ProductID        => ProdId
UnitPrice        => UntPrice
SalesOrderDetail => SalesOrdDtl
ProductID        => ProdId
UnitPrice        => UntPrice

Open in new window

This may be automatable if the names in the left column (existing names) are always being changed, and can never be in a subset of some other longer name.  For example if there is some other existing table or column (or view name, etc) that could be in the SQL code named "SomeOtherUnitPrice" then do a search and replace looking for "UnitPrice" and changing it to "UntPrice" will also change "SomeOtherUnitPrice" to "SomeOtherUntPrice", which would be a problem.

So you have to look at or think about would replacing all the occurrences of the first string that is changing to the new string ever NOT be the right thing to do.  If the answer is NO then you could do a mass search and replace.  Otherwise you may have to do it manually.

Either way you may want to use a tool that allows side by side comparison of the before and after code highlighting the differences, so that you can confirm the changes were correct.  I use "Beyond Compare" for things like this, but there are a couple of freeware Windows tools that work as well, like WinMerge.

Let me know if you think simple text replacements would work, and we can talk about possible tools / approaches to that.


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
techEverestAuthor Commented:
Thanks for your prompt reply. It didn't provide solution to my issue but I just downloaded diff tool and will proceed with it for now.
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

From novice to tech pro — start learning today.