SQL Change Automation supports SQL Compare-compatible filter rules, which provide a simple way to control the inclusion of objects 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. Authoring the filter rules themselves can be done using the SQL Compare UI or by editing the filter files directly within a text editor (e.g. using the Visual Studio Xml document editor).

Why use filters?

By default, SQL Change Automation will import all objects from the connected database when you use the SQL Change Automation tool-window to generate scripts. Although you can selectively un-check objects that you do not wish to import, thereby excluding them from your deployment, it may be desirable to exclude certain objects from the import tool altogether (e.g. to prevent any changes from being accidentally re-selected for import).

It may be desirable to have SQL Change Automation exclude entire schemas from the tool that may, for example, contain objects that are controlled by a third-party.

How it works

Use SQL Compare's filter designer to configure filter rules, and then export the rules to a file called Filter.scpf within the root of the SQL Change Automation project folder (i.e. alongside the .sqlproj file). When the SQL Change Automation tool-window is used to import new changes, the rules defined within the filter file will be applied: any objects that do not satisfy the rules will be excluded from the generated migrations, programmable objects and/or offline schema model.

Initial setup

1. Open the project within Visual Studio and, within the SQL Change Automation tool-window, click Refresh.
   Take note of the database connection (server & database) mentioned within the tool-window
   
2. Launch SQL Compare and specify the following settings and click Compare:
   • Source server: <Project server> 
   • Source database: <Project database> 
   • Destination server: <Project server>
   • Destination database: <Project database>_<User>_SHADOW

3. Click Actions... Filter Setup Pane (CTRL+L) 
   Using the Filter editor, specify any filter rule customizations as needed. The list of objects in the comparison pane will automatically update to reflect rule changes.
   
4. Within the Filter pane, click the save icon (Save the current filter) and specify the following path to save the filter file to the SQL Change Automation project folder:
   <Project folder>\Filter.scpf

5. Within Visual Studio, close and re-open the project/solution.
   The filter file should automatically be added to the project. If opened for manual editing, the filter file will look like this (note the customization of the Table exclusion rule):

   Filter.scpf sample

   <?xml version="1.0" encoding="utf-8" standalone="yes"?>
   <!--
   SQL Compare
   SQL Compare
   Version:13.1.3.5405-->
   <NamedFilter version="1" type="SQLCompareFilter">
     <!--This filter can be loaded using the SQL Compare UI or used with a SQL Compare command line session using the /filter switch-->
     <FilterName>Filter</FilterName>
     <Filter version="1" type="DifferenceFilter">
       <FilterCaseSensitive>False</FilterCaseSensitive>
       <Filters version="1">
         <None version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </None>
         <Assembly version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </Assembly>
         <AsymmetricKey version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </AsymmetricKey>
         <Certificate version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </Certificate>
         <Contract version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </Contract>
         <DdlTrigger version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </DdlTrigger>
         <Default version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </Default>
         <ExtendedProperty version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </ExtendedProperty>
         <EventNotification version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </EventNotification>
         <FullTextCatalog version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </FullTextCatalog>
         <FullTextStoplist version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </FullTextStoplist>
         <Function version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </Function>
         <MessageType version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </MessageType>
         <PartitionFunction version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </PartitionFunction>
         <PartitionScheme version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </PartitionScheme>
         <Queue version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </Queue>
         <Role version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </Role>
         <Route version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </Route>
         <Rule version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </Rule>
         <Schema version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </Schema>
         <SearchPropertyList version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </SearchPropertyList>
         <SecurityPolicy version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </SecurityPolicy>
         <Sequence version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </Sequence>
         <Service version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </Service>
         <ServiceBinding version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </ServiceBinding>
         <StoredProcedure version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </StoredProcedure>
         <SymmetricKey version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </SymmetricKey>
         <Synonym version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </Synonym>
         <Table version="1">
           <Include>False</Include>
           <Expression>((@NAME = 'MyTable'))</Expression>
         </Table>
         <User version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </User>
         <UserDefinedType version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </UserDefinedType>
         <View version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </View>
         <XmlSchemaCollection version="1">
           <Include>True</Include>
           <Expression>TRUE</Expression>
         </XmlSchemaCollection>
       </Filters>
     </Filter>
   </NamedFilter>

6. Within the SQL Change Automation tool-window, click Refresh. 
   Note that the list of objects is now reduced to those specified in the filter rules.

Editing the filter rules

To edit the filter files after the initial setup has been performed, simply re-open the filter file within SQL Compare, make any necessary edits to the rules and click the Save icon within the Filter pane to overwrite the filter file. 

Customising the filter file path

In some situations, it may be desirable to specify a different filter path, depending on the calling context. For this purpose, SQL Change Automation provides the DatabaseObjectFilterPath MSBuild property setting.

For example, when configuring your build server to report on schema drift in the QA environment, you might want to exclude all user objects. But when reporting on drift in production, you may want to report on all objects within the environment. This can be achieved by utilizing a different filter file per environment (Filter_QA.scpf, Filter_Prod.scpf etc).