How to replace linked server calls in SQL server?

[intackchoi]

I have two SQL Server database instances - let's call them server A and B.

Sadly, as the user traffic decreases, there is now no need for running two servers anymore. So I'm planning to merge databases on server B to A.

But here's the problem: there are thousands of stored procedures and triggers on server A that connects to server B through linked server and vice versa.

When merged, there is no need to use linked server so I'm going to have to replace all of them.

It might be possible to keep linked server and make it to point itself? But it seems like a bad practice.

How can I effectively replace all of them? Do I have to write thousands of ALTER SQL scripts?

[#2 · Accepted solution]

@intackchoi , use SSDT = SQL Server Database Tools, create a database project, import database design, replace all references to the linked server and deploy the changes to your SQL Server.

See Import into a Database Project