How to Use Custom Filters in SQL Compare

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:

These were the tables that held various information in the system. Trying to find data 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. However, 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.

Using Filtering

The key to using SQL Compare is filtering. 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 restored 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.