Filtering SQL Compare to a Schema

One of the things that numerous clients have some to Redgate about is allowing a team of developers to work on a single shared database without creating conflicts. While this isn’t necessarily a simple thing to do, it is easy if you structure the projects appropriately. This post looks at one of those ways, with schema filtering.

In a recent case, a customer had multiple schemas, and they wanted a SQL Change Automation project scoped to a single schema. Easy enough to do, as a new project gives you a place to pick your filter, but in order to do that, you need to create it first in SQL Compare. This post looks at how to do that.

The Scenario

I created a demo database with a couple schemas inside, and a few objects inside. I had a blank database to simulate a new QA environment. I opened SQL Compare and created a new project, pointing to these two databases. From here, I want the initial comparison. you can see this below, with a number of objects listed in different schemas.

2021-03-12 17_09_13-SQL Compare - E__Documents_SQL Compare_SharedProjects_(local)_SQL2017.SimpleTalk

On the left is the filter pane, and near the top is a “Custom filter rules” link. I clicked this to open up the filter dialog. In here, I see a default set with no rules.

2021-03-12 17_10_21-Edit filter rules

I added a new rule to set the schema name equal to “Sales”, to filter all objects away that do not exist in Sales. This is to give me a “Sales” project that developers can use in this schema.

2021-03-12 17_10_30-Edit filter rules

I click OK, and then I refreshed the comparison. I saw this:

2021-03-12 17_11_48-SQL Compare - E__Documents_SQL Compare_SharedProjects_(local)_SQL2017.SimpleTalk

I still have the Inventory schema listed. That’s not ideal, as this could be confusing to juniors developers. Also, depending on the comparison options, this could allow dependent objects to leak into this project.

Filtering Schemas

Schemas are not owned by themselves, and are separate. As a result, I need an additional item in this filter. If I scroll down the left side, there is a “schema” checkbox. If I put my mouse on this, I can see that there is an “edit” link.

2021-03-12 17_13_24-SQL Compare - E__Documents_SQL Compare_SharedProjects_(local)_SQL2017.SimpleTalk

I click that and then create my filter rule. In this case, I want the “Object name” to equal Sales. The object is the schema.

2021-03-12 17_14_22-Edit filter rules

Once I click OK, the Inventory schema disappears.

2021-03-12 17_15_08-SQL Compare - E__Documents_SQL Compare_SharedProjects_(local)_SQL2017.SimpleTalk

I need to repeat this for each schema that I want to build a filter for. Note that there isn’t a “save as” for filter files, so I need to manually copy each one in the file system when it is complete, or create a new SQL Compare project.

Summary

Once I have the filter files, I can load the appropriate one into each SCA project, allowing me to have projects that only see a small portion of the objects in a database.

One other thing I often do here is remove global type objects, like users, from the project as well by unchecking those boxes in the filter pane.

SQL Compare is an amazing tool that underpins much of what makes DevOps tools and automation work well with Redgate tools. I’d urge you to give it a try if you never have. You’ll be amazed as how much is helps you get work done quickly.

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 )

Google photo

You are commenting using your Google 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.