Use Objects Filter

Objects Filter enables you to include or exclude database objects in schema comparison results and synchronization. You can filter objects by type, name, or other criteria. You can also apply multiple filters and create custom filters using Filter Editor.

With Objects Filter, you can:

Select database objects for synchronization.
Create or modify filter rules.
Open and apply custom filters.
Revert filter settings to the last saved or loaded state, or reset them to their default values.

Objects Filter

Open Objects Filter

Objects Filter opens automatically after you run a schema comparison.

If Objects Filter is closed, you can open it in one of these ways:

On the Comparison toolbar, click .
Right-click the schema comparison grid and select Objects Filter.
Press Ctrl+L.

Note

Objects Filter retains your filter selections even when it is closed, and preserves them when you update or re-run a schema comparison.

You can use the toolbar to manage filters.

Objects Filter toolbar

The table describes the toolbar options.

Icon	Name	Description
	Open an existing filter	Loads a previously saved custom filter.
	Save	Saves the schema comparison object filter to a .scflt file, either by creating a new file or by updating the current filter file.
	Delete the current file	Deletes the current user-defined schema comparison object filter file.
	Discard Changes	Reverts the filter settings: If the filter was saved to a separate file, dbForge Studio restores the settings to the state they were in when the filter was saved. If the saved .scomp file was reopened, and the filter was restored from it, dbForge Studio restores the settings to the state they were in when the file was loaded.
		Closes Objects Filter.

Note

The is available for filter settings saved to the filer or opened from it but is unavailable for the Nothing excluded and Custom* filters.

The is unavailable only for the Nothing excluded filter.

Warning

If you reload the same filter, the message This filter has already been loaded. Overwrite? appears. Click Yes to confirm the operation. Click No to cancel.

The Filter box shows the available filters:

Nothing excluded – The default filter.
Custom* – A modified version of the Nothing excluded filter.
User-defined filter (for example, MyFilter) – A filter saved as an .scflt file and loaded by a user.

An asterisk (*) in the filter name indicates unsaved changes.

You can select a filter directly from the Filter box.

Select a filter from the Filter box

Warning

Switching to another filter or closing Objects Filter discards any unsaved changes, including changes to Custom* filters. Save your changes before switching filters or closing the tool.

When you edit a filter, you can either overwrite the existing filter or save it with a new name to create a new filter.

Objects Filter tree

The Objects Filter tree lists the database objects on the specified server. Objects appear in alphabetical order.

Objects Filter tree

Filter objects by types

By default, all object types are included in the comparison results and synchronization.

Include object types

To include an object type, select the checkbox for the corresponding type, or click its name.

To include all object types, select All object types, or click its name.

Exclude object types

To exclude an object type, clear the checkbox for this type, or click its name.

To exclude all object types, clear All object types, or click its name.

Note

Excluding an object or object type removes it from the comparison results and prevents it from being selected for synchronization.

Warning

If an object selected for synchronization references an excluded object, you can include the excluded object later on the Dependencies tab of the Schema Synchronization Wizard.

Save filters

You can save the schema comparison object filter to an XML file with the .scflt file extension.

To save a filter:

1. On the Objects Filter toolbar, click , then select Save or Save As.

2. In the Save As dialog, specify the file name and select the folder to store the file.

3. Click Save.

The .scflt file is a regular XML file that can be edited with any text editor. It stores all object types, their synchronization status (True for included, False for excluded), and any associated filter rules. The filter settings are located within the FiltersCollection tag.

Checked – Shows whether the database object type is included (True) in the comparison or not (False). It can be left empty when included but modified with a certain filter rule.
Filter – Contains an object name mask to filter objects by. It is empty by default.
Include – Indicates whether the filter rule includes (True) or excludes (False) objects.
ObjectName – The name of the database object type that the filter refers to.

File with all object types, synchronization status, and associated filter rules

Create a filter rule

1. In the Objects Filter tree, click for an object type to open the Filter Editor dialog.

2. In Filter rule for, select a specific object type to apply the filter, or select All object types to apply the filter to all objects.

3. Select Include objects if (default) to include objects that meet the condition, or select Exclude objects if to exclude objects that meet the condition.

4. Click the logical operator placeholder, then select the operator:

And – Displays the records when both conditions are True.
Or – Displays the records when at least one of the conditions is True.
Not And/Not Or – Reverses the logic of the specified conditions.

Note

When you click the upper-level operator, you can also add a new condition or a new condition group, or clear all conditions.

5. To add a filter rule, click .

Add a new condition or group

6. Click the Source Object placeholder and select the item.

The table describes the source objects supported in the editor.

Name	Description
Operation	A synchronization operation for the target database: Create – Adds the object to the target database. Update – Modifies the object in the target database to match the source. Drop – Removes the object from the target. None – No changes are applied to the target.
Source Object	A name of the object in the source schema.
Target Object	A name of the object in the target database.
Source Owner	A schema to which the object belongs in the source database.
Target Owner	A schema to which the object belongs in the target database.
Status	A status of the object as the result of schema comparison. Only in Source – Objects that exist only in the source schema and can be created in the target schema during schema synchronization: Different – Objects that exist in source and target schemas but have different DDL definitions. Only in Target – Objects that exist in the target schema and can be dropped from the target schema during schema synchronization. Identical – Objects that are identical and exist in both schemas. Inconsistent – Objects that exist in both schemas but are inconsistent and cannot be compared or synchronized.
Type	A type of the database object.

Tip

For instructions on how to exclude objects with the Only in Target status from the schema comparison, see Exclude all objects with the Only in Target status.

Select the source object for the condition

7. Click the operator placeholder (default: =) and select the filter operator for the condition, such as =, <, or <>.

Select the comparison operator

8. Click the value placeholder and select the value from the list.

9. Click OK to save the changes.

Remove conditions

Remove a condition or a condition group in one of these ways:

Click for the required condition.
Press the Minus Sign key.

Remove condition groups

To remove a condition group, click for the required condition group.

Note

Pressing the Minus Sign key removes only one filter rule in the group.

Reset to default state

To clear all conditions and revert the editor to its default state, in the Filter Editor, click Reset.

Discard changes

To close the dialog without saving changes, click Cancel.

Revert filter settings

To revert filter settings to the last saved or loaded state, or to the defaults values, on the Comparison toolbar, click .

Filters and synchronization

A filter determines which objects appear in the comparison results and are available for synchronization.

When you exclude an object or object type by using a filter, it does not appear in the comparison results and cannot be selected for synchronization.

If an excluded object is referenced by an object that you selected for synchronization, dbForge Studio notifies you about the dependency. You can then choose to synchronize the excluded object on the Dependencies tab of the Schema Synchronization wizard.

Filters and search

If an object matches the search string but is excluded by the filter, it does not appear in the comparison results.

Filter objects from the command line

You can filter objects from the command line using the /filter switch:

/filter:<filepath>

where <filepath> is the path to the file (.scflt) that stores the filter.

For more information, see Switches used in the command line.

Example: How to compare two database schemas while ignoring differences in users and permissions

Let’s illustrate Objects Filter with the following case where Schema Compare serves as part of a CI/CD cycle. After upgrading the database, you need to verify that the upgrade was successful. To do this, compare its schema with the schema of a reference database. Because the reference database contains different users, you want to ignore differences in users and permissions during the comparison.

To compare database schemas:

1. On the toolbar, click New Schema Comparison.

2. On the Source and Target page, select the source and target databases you want to compare.

3. Click Compare.

Select databases

The schema comparison document opens and shows that users are included in the comparison. It also displays permissions granted on certain tables. For example, the DeputyDirector_JaneSmith user has specific privileges on the Customers table.

Schema comparison document

4. Start a new schema comparison.

Select databases for schema comparison

5. On the Options page, enter permissions in the search box.

The search results display two options, Ignore permissions and Ignore users’ permissions. Select both options.

Note

In the Search box, you can use the following keyboard shortcuts:

Ctrl+A to select all.

Ctrl+Left Arrow (←) to move the caret to the beginning of the word.

Ctrl+Right Arrow (→) to move the caret to the end of the word.

Ctrl+Shift+Left Arrow (←) to select text from the current caret position to the beginning of the current word and move the caret to the beginning of the word.

Ctrl+Shift+Right Arrow (→) to select text from the current caret position to the end of the current word and move the caret to the end of the word.

Ctrl+Backspace to delete the word to the left of the cursor.

Options

6. Click Compare.

Note

We can use the command line to run automated comparison and synchronization with ignored permissions by setting the corresponding options: /IgnorePermissions:Yes /IgnoreUserPermissions:Yes.

The command will look as follows:
dbforgesql.com /schemacompare /source connection:"Data Source=demo-mssql\SQLEXPRESS02;Initial Catalog=BicycleStoreDev;Integrated Security=False;User ID=sa" /target connection:"Data Source=demo-mssql\SQLEXPRESS02;Initial Catalog=BicycleStoreProd;Integrated Security=False;User ID=sa" /IgnorePermissions:Yes /IgnoreUserPermissions:Yes /sync
You can also save a schema comparison file (.scomp) that will contain ignored permissions. You can then reuse this file for automated comparison and synchronization from the command line. The command will look as follows:
dbforgesql.com /schemacompare /compfile:"C:\jordansanders\Comparison.scomp" /sync```

After the schema comparison completes, the permissions on the Customers table are ignored in the schema comparison document.

Get the comparison results - ignored permissions

7. To ignore users, open Objects Filter by clicking on the Comparison toolbar, or pressing Ctrl+L.

The users are displayed in Only in Source.

Open the Objects Filter

8. In Objects Filter, clear the checkbox for Users to ignore them during synchronization.

The results are immediately updated in the Schema Comparison document. Only in Source doesn’t contain users.

Filter out users

9. Save our filter to a .scflt file.

Save the file with filter settings

10. Exclude all users in Source Objects that begin with Manager. Go to the filter settings and configure that.

Filter out Managers

11. Click OK.

The Schema Comparison document is immediately updated. As you can see, there are no Managers in Only in Source. They have been excluded from the comparison and will be excluded from synchronization.

Filtered out managers

This is how the .scflt file looks like when Managers are excluded.

The updated .scflt file

Note

When you need to compare and synchronize our databases with the filter applied, you can do this from the command line using the .scflt file:

dbforgesql.com /schemacompare /source connection:"Data Source=demo-mssql\SQLEXPRESS02;Initial Catalog=BicycleStoreDev;Integrated Security=False;User ID=sa" /target connection:"Data Source=demo-mssql\SQLEXPRESS02;Initial Catalog=BicycleStoreProd;Integrated Security=False;User ID=sa" /IgnorePermissions:Yes /IgnoreUserPermissions:Yes /filter:"C:\jordansanders\Custom.scflt"/sync

Alternatively, use both the .scomp and .scflt files:

dbforgesql.com /schemacompare /compfile:"C:\jordansanders\Comparison.scomp" /filter:"C:\jordansanders\Custom.scflt"/sync