xSQL Schema Compare for SQL Server allows the user to define schema filters that determine which objects will be included or excluded in the comparison and synchronization process. The process of setting schema filters is fairly straight forward and does not need much explaining however, there is one critically important element that has the potential to create some confusion hence this article.
First, a schema filter expression is defined for a particular object type and contains one or more filter criteria. The schema filter expression has a parameter called "FilterValidationType" that can be set to "AtLeastOneCriteria" or "AllCriteria". Each filter criteria has an "Action" parameter that can be set to "Exclude" or "Include" meaning that the object that meets this particular criteria will be excluded or included.
So, let's consider the schema filter expression defined as below:
<SchemaFilterExpressions>
<SchemaFilterExpression ObjectType="StoredProcedure" FilterValidationType="AllCriteria">
<SchemaFilterCriteria Action="Exclude" FilterType="StartsWith" Criteria="sp_MSins" />
<SchemaFilterCriteria Action="Exclude" FilterType="StartsWith" Criteria="sp_MSdel" />
<SchemaFilterCriteria Action="Exclude" FilterType="StartsWith" Criteria="sp_MSupd" />
</SchemaFilterExpression>
</SchemaFilterExpressions>
Here is what might confuse things:
whether you have defined the criteria action as"exclude" or "include" behind the scenes xSQL Schema Compare for SQL Server translates the object filter expression into an include statement, in other words the final statement selects objects that will be included in the comparison.
So, the above schema filter expression translates into something like this:
SELECT all stored procedures WHERE [sp name] NOT LIKE 'sp_MSins%'
AND [sp name] NOT LIKE 'sp_MSdel%' AND [sp name] NOT LIKE 'sp_MSupd%'
Interpretation: all stored procedures the name of which starts with one of those three strings will be excluded whereas all other stored procedures will be included in the comparison.
What if you had set the FilterValidationType = "AtLeastOneCriteria"? Behind the scenes the expression would then translate into something like:
SELECT all stored procedures WHERE [sp name] NOT LIKE 'sp_MSins%'
OR [sp name] NOT LIKE 'sp_MSdel%' AND [sp name] NOT LIKE 'sp_MSupd%'Well, as you see when written this way this filter is as good as not being there at all since every stored procedure, regardless of the name will pass this validation and will be included in the comparison.
xSQL Schema Compare for SQL Server is free for SQL Server Express Edition and you can download it from:
http://www.xsql.com/download/package.aspx?packageid=10
Posts
