Click here to monitor SSC
  • Av rating:
  • Total votes: 10
  • Total comments: 0
Simon Cooper

Using the Filtering API with the SQL Comparison SDK

17 April 2009

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:

using System;

using RedGate.SQLCompare.Engine;

using RedGate.SQLCompare.Engine.Filter;


namespace FilterExample {

    class Program {

        static void Main() {

            // register and compare the two databases

            Database db1 = new Database();

            Database db2 = new Database();

            db1.Register(new ConnectionProperties("(local)", "WidgetDev"), Options.Default);

            db2.Register(new ConnectionProperties("(local)", "WidgetTest"), Options.Default);


            Differences diffs = db1.CompareWith(db2, Options.Default);


            // FILTER:

            // create a filter to exclude everything

            DifferenceFilter filter = new DifferenceFilter();


            // set the filter to include tables that have names starting with 'Widget'

            filter.SetObjectTypeFilter(ObjectType.Table, true, "@NAME LIKE 'Widget%'");


            // perform the filter, and display the results

            foreach (Difference d in filter.FilterDifferences(diffs)) {








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:


DifferenceFilter filter = new DifferenceFilter();

filter.SetObjectTypeFilter(ObjectType.Table, true, "@SCHEMA = 'Sales'");



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

bool included = filter.DifferenceIncluded(diff);

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:

(@SCHEMA = 'dbo' AND @NAME LIKE 'tbl%dev%')


AND NOT (@NAME != 'table1' OR @NAME LIKE 'table1%inc'))

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

DifferenceFilter filter = DifferenceFilterFactory.CreateFilter(false,

@"(@TYPE = 'table' AND @NAME = 'widget')

OR @TYPE = 'view'

OR (@TYPE = 'stored procedure'

AND (@SCHEMA = 'dbo' OR @NAME = 'proc1'))");

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:

OR<Difference> or = new OR<Difference>();

or.Add(new Condition(true, FilterOn.Name, FilterType.LIKE, "tbl%dev"));

or.Add(new Condition(true, FilterOn.Schema, FilterType.NEQ, "dbo"));

DifferenceFilter filter = new DifferenceFilter();

filter.SetObjectTypeFilter(ObjectType.Table, true, or);

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

bool? IFilterCondition<T>.ObjectIncluded(T obj)


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.

Simon Cooper

Author profile:

Simon joined Red Gate as a software developer in 2007. He has spent most of his time on SQL Compare 7 and 8, where he concentrated on the SQL Compare engine. Other projects have included the new Check for Updates and work on the internal shared libraries. He is currently researching and developing some new products for Red Gate to move into, which he can’t say too much about at the moment!

Search for other articles by Simon Cooper

Rate this article:   Avg rating: from a total of 10 votes.





Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.
Simple-Talk Database Delivery

Patterns & Practices Library

Visit our patterns and practices library to learn more about database lifecycle management.

Find out how to automate the process of building, testing and deploying your database changes to reduce risk and make rapid releases possible.

Get started

Phil Factor
Automatically Creating UML Database Diagrams for SQL Server

SQL Server database developers seem reluctant to use diagrams when documenting their databases. It is probably... Read more...

 View the blog

Top Rated

The Enterprise DBA Mindset as a Practical Problem-solving Approach
 In order to keep the demands of the job under control, any DBA needs to automate as many as possible of... Read more...

In-Memory OLTP - Row Structure and Indexes
 There are several decisions to be made when designing indexes for Memory-optimized tables in In-Memory... Read more...

In-Memory OLTP – Understanding Memory-Optimized Tables
 How do you get started with In-memory OLTP? Murilo Miranda first advises on the setup of the server and... Read more...

Automatically Creating UML Database Diagrams for SQL Server
 SQL Server database developers seem reluctant to use diagrams when documenting their databases. It is... Read more...

SQL Server Security Audit Basics
 SQL Server Server Audit has grown in functionality over the years but it can be tricky to maintain and... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

Temporary Tables in SQL Server
 Temporary tables are used by every DB developer, but they're not likely to be too adventurous with... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.