Search and Replace Three Part Names with SQL Compare

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.

2022-04-19 16_43_59-SQL Compare - E__Documents_SQL Compare_SharedProjects_(local)_SQL2017.SimpleTalk

If I have this option checked, as shown here:

2022-04-19 16_45_19-(local)_SQL2017.SimpleTalk_1_Dev v localhost.SimpleTalk_1_Dev.scp

Then nothing appears.

2022-04-19 16_45_31-SQL Compare - E__Documents_SQL Compare_SharedProjects_(local)_SQL2017.SimpleTalk

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.

2022-04-19 16_49_11-SQL Compare - E__Documents_SQL Compare_SharedProjects_(local)_SQL2017.SimpleTalk

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.

2022-04-19 16_50_34-Deployment

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.

2022-04-19 16_53_29-SCO485aa5e5566748149e0e8259930b6af0.sql - (local)_SQL2017.Compare2 (ARISTOTLE_St

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].

2022-04-19 16_54_56-SCO485aa5e5566748149e0e8259930b6af0.sql - (local)_SQL2017.Compare2 (ARISTOTLE_St

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.