SQL Server filtering

Flyway Desktop supports SQL Compare-compatible filter rules, which provide a simple way to control which objects are included in the schema model and within the script generation and reporting processes.  Filter rules provide the flexibility to allow both include and exclude rules to be specified for your project. If there are certain objects that you never want to track in version control and generate migrations for, then it is best to filter these objects out all together so they are not accidentally selected. 

Setting up a filter before the Baseline process will also exclude these objects when you generate a baseline script.

Set up filter rules

Watch this 4 minute video on Redgate University to see how to use filter files.  This covers the following: 

Currently, there is no GUI in Flyway Desktop to customize the filter file.  You can use SQL Compare to configure the Filter.scpf file or edit it directly.  We recommend using SQL Compare, which is included in your Flyway Enterprise license, so there are no syntax mistakes. 

Use SQL Compare's filter designer

  1. Launch SQL Compare.

  2. Select your Development Database as the source and target and run the comparison. 

  3. After running the comparison, use the blue open folder in the Filter panel on the left to open the Filter file from your Flyway project.  

    1. Note: You can find the location of your Flyway project's filter file by using the blue folder on the top right of Flyway Desktop.
  4. Save any change to the Filter file.  

  5. In the Flyway Desktop Version control tab, commit and push this filter file to the remote repository so all team members have the same filter settings.

Editing the filter file directly

You can edit the filter file in the root folder of your Flyway project in Notepad or another editor.  Save your changes to the file and then use the Version control tab in Flyway Desktop to commit and push this filter file to the remote repository so all team members have the same filter settings.

The Filter file uses standard SQL Server LIKE and NOT LIKE syntax, which supports the following wildcards.

_matches any single character
%matches any sequence of characters

[^<range | character list>]

matches a range or character list


<!-- Here we are only including tables that begin with tbl_ -->
<!-- Note that since the underscore character is interpreted as a wildcard, it needs to be escaped in square brackets to denote the character -->
    <Table version="1">
        <Expression>((@NAME LIKE 'tbl[_]%'))</Expression>

<!-- Here we are excluding tables that begin with test_ OR tables that begin with todo_ -->
    <Table version="1">
        <Expression>((@NAME LIKE 'test[_]%')) OR ((@NAME LIKE 'todo[_]%'))</Expression>

<!-- Here we are excluding tables that belong to a schema named 'test' -->
    <Table version="1">
        <Expression>((@SCHEMA = 'test'))</Expression>

Customizing the filter file path

A filter file will be picked up automatically if it is named Filter.scpf and in the root of the Flyway project. A different path can be specified if desired in the project settings file under capabilities.comparison.filter.

"capabilities": {
    "comparison": {
        "filter": "./Filter.scpf"

Didn't find what you were looking for?