Steve Jones

17 March 2017

• SQL Compare
• Schema Comparison Options

How to use custom filters in SQL Compare

You’re comparing the schema of two SQL Server databases, but you’re seeing lots of difference between objects that are not relevant to you. Sometimes, there are certain classes, or types, of database object that you need to exclude entirely from database schema comparisons and from any database deployment scripts. For example, sometimes you maintain a single database source, in version control, from which you deploy several different variations of the database, for different customers. In such cases, when running schema comparisons between the database source, and a customer’s production database, you’ll want to make sure that you include in the comparison only the objects that are required by that customer.

SQL Compare filters make this job a lot easier.  You can use them to get SQL Compare to ignore certain classes of object from a schema comparison, or include only specific objects, by name or by type. Phil Factor goes into a lot more details on use of filters here: Using Filters to Fine-tune Redgate Database Deployments. 

Using filters to customize schema comparisons

I used to work at JD Edwards, and in one of our ERP products we had a cumbersome database schema. There were tables like this:

Trying to find data in these tables, by the way, was an exercise in frustration, even for those of us that were familiar with it. After all, we couldn’t remember the name of every table and what it was used for. In any case, we also had many customers that would add their own tables and extensions to the database. That means we’d end up with tables like those at the bottom of this image. They might have names like Sales_xx, and GA_yyy:

Customers would want to essentially develop on a part of the database, and easily deploy their changes from a development system to a test system or a production database. Could they have easily just deployed certain changes with SQL Compare? Let’s see.

Setting up a filter

We can add a filter to our SQL Compare project to ensure that none of the tables that come with JDE are included in the project. One way of doing this is to filter based on the name of the object.

I’ve got two databases, which are the same. I know this because I restore them both from the same source.

I’ll add a few tables to my source database, the main JDE one, that are named like the tables shown above. I haven’t changed any of the JDE tables, so they show up as identical.

Let’s change one of the JDE tables, just to simulate an upgrade or patch. I’ll update the F002 table with a new column. When I refresh the project, I see this as a changed item.

I don’t really want to see any of the JDE tables in this project as I only want to see the changes to the items I’ve added. In the case of the JDE tables, they all end with a number, so let’s add a filter to remove this.

I can click the Actions menu and then the Filter Setup Pane, or press CTRL+L to get this.

When I do this, I see a long vertical pane on the left.

At the top, there is a Custom filter rules… link. When I click this, I can add additional filters beyond the simple checkboxes shown for object types.

There are a number of choices here, but I’ll go with Object name, and I’ll use a NOT LIKE comparison operator that includes wildcards. This gives me a filter like this:

When I click OK, I then see only my new objects listed.

Of course, I can add more filters that might help, with different logical choices. However, be careful. As with SQL, I want to be sure that I select the correct choices that would correspond to the T-SQL operations using =, !=, AND, OR, etc. I could also do this, but that seems more cumbersome:

I can save my filters with the project, or I can save the filters as their own file. This is useful if I want to use the filters in other Redgate products, such as SQL Source Control or DLM Automation. To save the filter, click the disk icon in the filter pane to bring up the save dialog.

SQL Compare filters are saved as a .scpf file, and you can send these around to other users, upload into other products, and use in all of your development and DevOps processes.

Summary

This post has shown how you can use SQL Compare in a database, even when you don’t work with all the objects and don’t want to clutter up the interface with extra information. In this case, using filters to limit the objects by name works well. There are many other great uses of the filter options you can utilize to customize your SQL Compare experience and make it work even harder for you.

If you don’t already use SQL Compare, you can explore the filtering option further with a fully-functional free trial.