Search and Replace Three Part Names with SQL Compare | Voice of the DBA

A customer had an interesting challenge in their codebase recently, and I thought this would make a good post. This involves SQL Compare and three part names.

The Scenario

A customer was using SQL Compare and they wanted to update the database name in a script to point to a new database. They had objects in a development database pointing to another development database. What they asked was: “How can we search and replace across all objects to change db1.dbo.mytable to db2.dbo.mytable?”

There are a couple ways that you can attack this problem, but I’ll show you what I think works best.

Ensuring Three Part Names Appear

There is a switch in a SQL Compare that allows you to include or exclude three part naming in the scripts. This is the switch that says “Ignore Server and Database Names in Synonyms”.

I don’t have that checked, and when I look at my comparison, I see this. There are two synonyms in these database, which you can see in the image, point to different targets.

If I have this option checked, as shown here:

Then nothing appears.

Make sure you don’t have this option checked. I’ll fix that first.

Next, I’ll add a couple more synonyms to show a few differences and then re-run the compare. Now I have 3 objects, 2 of which are different, one doesn’t exist in the comparison database.

Generate the Script

I want to generate a script. I’ll do that by pressing the “deploy” button. I’ll change the option from deploy to generate script. This will open in SSMS.

I click the “Open in editor” button and SQL Compare generates the script and opens SSMS. Warning, it opens a new version of SSMS, not the existing one I have open. Grrr.

I see my script here, which is good.

I can search and replace now, looking to change the database name. Hard to see in the image below, but I’m searching for this:

FOR [AdventureWorks2017].

and replacing it with:

FOR [sandbox].

This will set the synonyms to the new target in the dev environment. In this case, I’m changing from AdventureWorks2017 to Sandbox.

The last thing to do if this script is to set up a new database is to remove the synonym drop statements. We can’t alter synonyms, so we need to drop them. That will be an error in this script if the synonyms don’t exist. Since this runs as a transaction, the entire thing will fail.

This shows a simple way to help create a script that can be used to refresh a dev environment with the proper schema settings when synonyms or other three part naming is in use.