17 April 2009

Using the Filtering API with the SQL Comparison SDK

Red Gate's SQL Comparison SDK provides a means to compare and synchronize database schemas and data from within your own applications, with a greater degree of control than the command line interfaces. Filters, introduced in V8.0, provide an easy way of limiting which database objects you deal with, and can be created with any arbitrary expression in a SQL-like syntax.

One of the new features in SQL Compare 8.0 is the ability to filter the objects displayed in the comparison results screen by various conditions:


In this article, I’ll give an overview of how you can use the filter in your own code.

Filtering Basics

Let’s jump in straight at the deep end, and give a simple example of using the filter on the differences from a comparison:

Here, the bit we’re interested in is below the FILTER: comment. The DifferenceFilter class is the one that manages the filter expressions and performs the filtering. A separate filter can be set for each object type recognised by SQL Compare, as well as a global condition (specified by ObjectType.None). Each object type can be set as unconditionally included, unconditionally excluded, or conditionally included. This corresponds to the three-state checkbox in the filter panel in the SQL Compare UI, and there are methods on the DifferenceFilter class to set each of these filter conditions. For instance, to include all views, stored procedures, and tables that have a schema of ‘Sales’, you would do something like this:

you can also check whether a particular difference is included by the filter, rather than using the FilterDifferences method:

The boolean argument to SetObjectTypeFilter determines if the filter expression is for inclusion or exclusion – if true, objects will be included if they match the filter; if false, objects will be excluded if they match the filter. There are also constructors on the DifferenceFilter class to determine if the filter matches case-sensitively, and if the filter includes or excludes everything by default.

Filter expressions

The filter expressions have a simple SQL-like syntax. Individual clauses are in the form @NAME != 'table', just like in a WHERE clause. The two object properties you can test against are @NAME and @SCHEMA, corresponding to the object name and schema. The operators you can use are ‘=’, ‘!=’, ‘LIKE’, and ‘NOT LIKE’. The = and != test against a simple string value, the LIKE and NOT LIKE clauses test against patterns with the % symbol, with the same meaning as in SQL (Currently, _ is not a regular expression symbol used for matching). These clauses can be strung together with any combination of ANDs, ORs and NOTs, with the same operator precedence as in SQL, and can be arbitrarily complex:

You can also create a DifferenceFilter using a global filter expression, using the DifferenceFilterFactory.CreateFilter method:

The @TYPE property is similar to the @NAME and @SCHEMA, except you can only use ‘=’ and ‘!=’ conditions on it, and it only accepts the full object names as values, for instance 'user defined type' or 'symmetric key'. Given the global expression, the DifferenceFilterFactory will separate the expression into different object types (yes, even if there’s a @TYPE right in the middle of a nest of brackets) and create a DifferenceFilter corresponding to that expression.


Alternatively, if you don’t wish to deal with expression strings directly, you can create the object tree for each object type directly:

The relevant classes are:

  • IFilterCondition<T> (the root of the object hierarchy)

  • OR<T>

  • AND<T>

  • NOT<T>

  • Condition

You will notice that all the classes apart from Condition are generic – if you create your own leaf condition for the object type you’re considering, you can use the filter types to filter objects other than Differences.

The main method that does the filtering is

Implementations of this method return a nullable bool indicating if the given object will be included, excluded, or if the filter condition does not apply (for instance, schema conditions on non-schema objects).

You can access the filter conditions in a DifferenceFilter using the DifferenceFilter.GetFilter method and access the Filter property on the ObjectTypeFilter returned. The corresponding string representation can also be returned using the ToString method on a filter condition.


Filters provide an easy way of limiting which database objects you deal with, and can be created with any arbitrary expression in a SQL-like syntax. Alternatively, you can create the filter expression trees yourself, which is an easier way of integrating with your own front end to the SDK. There are many more ways of using filters than the examples in this article; hopefully this has given you some ideas on using filters in your own code.

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 11660 times – thanks for reading.

  • Rate
    [Total: 10    Average: 4.2/5]
  • Share

Simon Cooper

View all articles by Simon Cooper

Related articles

Also in BI

Relational Algebra and its implications for NoSQL databases

With the rise of NoSQL databases that are exploiting aspects of SQL for querying, and are embracing full transactionality, is there a danger of the data-document model's hierarchical nature causing a fundamental conflict with relational theory? We asked our relational expert, Hugh Bin-Haad to expound a difficult area for database theorists.… Read more

Also in Database

SQL Server System Functions: The Basics

Every SQL Server Database programmer needs to be familiar with the System Functions. These range from the sublime (such as @@rowcount or @@identity) to the ridiculous (IsNumeric()) Robert Sheldon provides an overview of the most commonly used of them.… Read more

Also in Source control

PowerShell Desired State Configuration: LCM and Push Management Model

PowerShell's Desired State Configuration (DSC) framework depends on the Local Configuration Manager (LCM) which has a central role in a DSC architecture. It runs on all nodes that have PowerShell 4.0 or above installed in order to control the execution of DSC configurations on target nodes. Nicolas Prigent illustrates the role of the LCM in the 'Push' mode of configuring nodes.… Read more

Also in SQL

The Comeback of Migrations-Based Deployments

With database deployments, not all script-based processes are equal. Some use change scripts in a free-and-easy way, and some, which are normally called 'migrations-based approaches', have more discipline around them. In this article, Redgate Product Manager Elizabeth Ayer covers 'migrations', and shows some of the benefits that have come with new tooling which is specifically designed to assist the change script processes.… Read more

Join Simple Talk

Join over 200,000 Microsoft professionals, and get full, free access to technical articles, our twice-monthly Simple Talk newsletter, and free SQL tools.

Sign up