An entity filter is a new feature introduced in the version 4 of the command line that replaces the schema filters supported by the previous versions. It allows you to include or exclude database objects based on some predefined criteria.
Schema filters supported by the previous versions of the command line are depreciated and will be removed in a future release.
An entity filter is composed by groups combined with an operator: AND/OR. Each group contains conditions, which act as the filter criteria. When the filter is applied, the conditions are evaluated against the name or the schema of a database object. If the object meets the condition criteria, it is included in the comparison, otherwise it is excluded.
The following xml fragment defines a filter that selects views the name of which starts with the prefix v_emp:
<EntityFilters>
<filter entitytype="View">
<condition>
<conditiontype>StartingWith</conditiontype>
<expression>v_emp</expression>
</condition>
</filter>
</EntityFilters>
The following xml fragment defines a filter that contains two conditions. It selects tables the name of which does not start with "#" and "##":
<entityfilters>
<filter entitytype="Table">
<group conditionoperator="And">
<condition>
<conditiontype>NotStartingWith</conditiontype>
<expression>#</expression>
</condition>
<condition>
<conditiontype>NotStartingWith</conditiontype>
<expression>##</expression>
</condition>
</group>
</filter>
</entityfilters>
The command line provides samples for the most common entity filter scenarios. You will find them in the command line sample folder under \Program Files\Idera\SQL comparison toolset\Command Line Samples\Schema Compare\EntityFilters.
The structure of the entity filters supported by the command line xml config file is as follows:
<entityfilters exclusionrule="ExcludeAlways|ExcludeIfPossible">
<filter entitytype="object_type" groupoperator="And|Or">
<group conditionoperator="And|Or" includematches="true|false">
<condition casesensitive="true|false" namecondition="true|false">
<conditiontype>condition_type</conditiontype>
<expression>expression</expression>
</condition>
</group>
</filter>
</entityfilters>
| xml Element/Attribute | Description | Usage |
|---|---|---|
EntityFilters | The starting element of the entity filters. | |
ExclusionRule: ExcludeAlways|ExcludeIfPossible | Determines the rule that should be used to exclude objects that don't meet the filter criteria with respect to dependencies:
| Optional. Default value is ExcludeAlways. |
Filter | Starts a filter element. Multiple filters are supported, each for a specific object type. | |
EntityType | Indicates the type of the database objects associated with the filter. Nearly all database are supported, except for the following:
| Required. |
GroupOperator: And|Or | Determines the operator that combines the groups that participate in the filter. | Optional. Default value is And. |
Group | Starts a filter group. Multiple groups can be included in a single filter. | Optional when the filter contains only one condition, otherwise required. |
ConditionOperator: And|Or | Determines the operator that combines filter conditions associated with a group. | Optional. Default value is Or. |
IncludeMatches: true|false | Indicates whether the database objects that meet the group criteria should be included in the comparison. | Optional. Default value is true. |
Condition | Starts a filter condition. | Required. |
CaseSensitive: true|false | Indicates whether the condition should honor the character case. | Optional. Default value is false. |
NameCondition: true|false | Determines whether the filter condition should be evaluated against the name of a database object. If false, the condition is checked against the schema. | Optional. Default value is true. |
| ConditionType | One of the following condition types:
| Required. |
Expression | A text expression that defines the condition criteria. | Required. |
The following xml fragment is a more complete example that defines three entity filters:
- The first filter creates two conditions that select database views starting with v_emp or v_cust. Both conditions are case-sensitive.
- The second filter selects stored procedures that belong to HumanResource or Personal schema. In addition to schema, the procedure name should contain at least one digit.
- The third filter selects functions that do not end with _temp and _tmp
<entityfilters exclusionrule="ExcludeAlways">
<filter entitytype="View">
<group conditionoperator="Or">
<condition casesensitive="true">
<conditiontype>StartingWith</conditiontype>
<expression>v_emp</expression>
</condition>
<condition casesensitive="true">
<conditiontype>StartingWith</conditiontype>
<expression>v_cust</expression>
</condition>
</group>
</filter>
<filter entitytype="StoredProcedure" groupoperator="And">
<group conditionoperator="Or">
<condition namecondition="false">
<conditiontype>EqualsTo</conditiontype>
<expression>HumanResource</expression>
</condition>
<condition namecondition="false">
<conditiontype>EqualsTo</conditiontype>
<expression>Personal</expression>
</condition>
</group>
<group>
<condition namecondition="true">
<conditiontype>RegExMatching</conditiontype>
<expression>\d+</expression>
</condition>
</group>
</filter>
<filter entitytype="UserDefinedFunction">
<group conditionoperator="And">
<condition>
<conditiontype>NotEndingWith</conditiontype>
<expression>_temp</expression>
</condition>
<condition>
<conditiontype>NotEndingWith</conditiontype>
<expression>_tmp</expression>
</condition>
</group>
</filter>
</entityfilters>
Total compare from data to schema with IDERA SQL Comparison Toolset. Learn more > >