How to narrow schema comparison and synchronization to affected objects only (4215648)

Applies to
ApexSQL Diff

Summary
This article explains how to use filtering options in ApexSQL Diff to narrow down schema comparison and synchronization to affected objects only.

Description

In cases when schema comparison and synchronization need to be narrowed down to desired objects, ApexSQL Diff offers various filtering options. Additionally, these filtering options can reduce the comparison and synchronization time, and size of the schema synchronization script.

ApexSQL Diff offers the following filtering options that can be divided in two groups:

1. The pre-comparison filter options:
   1. The Object filter options – filter object types and individual objects
2. The post-comparison filter options:
   1. The Object filter – filter objects by difference type and object type
      • The Filter editor – filter object types by schema and object name conditions
   2. The Difference filter – filter objects differences based on several attributes
   3. The Results grid column filters – filters out columns in the Results grid

Pre-comparison filtering

Once the source and destination data sources are set in the New project window, expand the Advanced options and click the Object filter tab:

In the Object filter tab, all SQL Server object types will be shown and by default all of them are checked:

Quick tip: If none of the Object filter options isn’t used, all object types and individual objects will be included in the comparison and synchronization process

The following filtering can be applied in the Object filter tab:

1. Check/uncheck the check box on the left side of the object type, to include/exclude specific object types:



2. When the object type node is clicked in the left panel, a list of available objects for the selected object type is displayed in the grid and by default all objects are checked. There are five ways to filter individual objects:
   1. Manually check/uncheck individual objects in the object list:



3. Column filtering of the object list offers three ways of filtering:
   • Filter checked/unchecked objects in the list with the Show only checked/unchecked objects buttons above the object list:

     

   • Filter object list by using the filter for the Name column, by using the Values or Text filters tabs:

     

   • Filter object list by using the filter for the Schema column, by using the Values or Text filters tabs:

     

4. The Filter editor feature can filter out the object list by using multiple conditions, so that specific objects are shown. To initiate the Filter editor window, click the Edit filter button in top-right corner of the Object filter tab:

   

Quick tip: Object filtering may speed up the comparison process, but ApexSQL Diff cannot detect dependent objects if they are excluded from the comparison, which may result in error(s) during the synchronization process

Post-comparison filtering

The comparison results displayed in the Results grid can be filtered using:

1. The Object filter – allows users the following filtering:
   1. To filter objects shown in the Results grid by their difference type, the following check-boxes can be used:
      • Different – displays objects that exist in both the source and the destination, but there are differences in their structure
      • Missing – displays objects that exist only in the source
      • Additional – displays objects that exist only in the destination
      • Equal – displays objects that exist in both the source and the destination, but their structure is equal

        

   2. Filtering of object types shown in the Results grid can be done by selecting the object type checkboxes in the Object filter:

      

      Grouping options can be used to choose in what order object types will be displayed in the Object filter and for easier viewing.

   3. The Filter editor is used for the specific filtering when setting up a schema or name condition for an object type. For example, the comparison results are shown in the Results grid and there is a need to see only tables with schema name Production and that objects name contains word Product:

      

      To set the condition with the Filter editor, select the Tables object type in the Object filter panel and click the Filter button next to it:

      

      The Filter editor window is shown and criteria can be set to And/Or or to Not And/Not Or, in order to display objects that meet the condition:

      

      The filter criteria can be built by using the drop-down menu for each field and by using the to add condition or to remove it. As mentioned above, the filter criteria will be set that object name contains word Product and schema name is Production:

      

      After setting up the filter criteria and the OK button is clicked, the Results grid will show tables that only meet the previously set filter criteria:

      

      Filter management options in the Object filer panel allow to save the current filter settings for later use, open saved ones or clear current object filter selection:

      

2. The Difference filter is used to affect what’s considered different within results, since objects differ based on several attributes (i.e. two objects can be the same except for their owners):

   

   In the following example, DDL trigger in source and destination differ only in extended properties, but that doesn’t need to be taken into consideration as a real difference, so it can be excluded:

   

   To exclude extended properties for DDL trigger, locate it in the Difference filter panel, uncheck them, and it will be instantly applied on the Results grid. In this case, since the only difference was in the extended properties, the DDL trigger won’t be shown in the Results grid:

   

3. The Result grid column filters can be accessed with a right-click on any column name:

   

   To learn more about the Results grid’s filters, check out the following articles:
   • Filtering rows in grids
   • Grouping grid rows
   • Managing grid columns
   • Managing rows in grids
   • Searching data in grids
   • Sorting columns

FAQs

Q: What will happen if I exclude dependent object?

A: It depends on when an object was excluded. If an object was excluded:

• Before the comparison process (in the Object filter tab) – dependent object won’t be picked up in the Dependencies step of the Synchronization wizard and it may cause failure in the synchronization process
• After the comparison process (in the Results grid) – dependent object will be picked up in the Dependencies step of the Synchronization wizard, if the Include dependent objects option is checked

Q: Can I save object filtering and reuse it later?

A: Yes. If any object filtering is set, it can be saved by saving the project file. Also, object filtering can be applied when using a project file in the CLI

Q: Can I set the same filter criteria for all object types in the Object filter panel vs. setting it up for each object type?

A: Yes. For example, if you want to set filter criteria to show all object types with schema name Person, select the All objects in the Object filter panel, click the Filter button next to it, and enter the filter criteria to And schema name equals Person: