Enhancement Request: Excluding objects by name/pattern
tnimitz
Posts: 6
Background: I currently work with a production environment that uses replicated databases, but also work with non-replicated development and test environments. My problem is that these databases do not compare cleanly using SQL compare. Specifically, replication triggers and other objects show up as changes, when I only care about the user-defined aspects of the database. I assume I am not the only one with this problem.
It would be useful to have a set of options to exclude objects from comparison based on names. Patterns could be specified separately for each type of object. For some objects (such as triggers), qualified names might also be supported for matching both the object name (trigger) and the parent object name (table).
For example, most replication objects could be excluded using options similar to the following:
Exclude Triggers: %.ins_%, %.upd_%, %.del_%
Exclude Stored Procedures: sp_sel_%
Exclude Roles: MSmerge_%
This may not be a complete list, and might change with SQL versions, but the user could update the filters as needed.
This would allow clean compares between replicated databases and would also allow synchronization of schemas from replicated databases to non-replicated databases. This could also handle situations not involving replication.
There may be situations where a user might prefer to use different filters for difefrent database compares, so it would also be useful if these options could be stored in the project file.
(I am posting this as an alternative to a general "Exclude replicated objects and triggers" option.)
It would be useful to have a set of options to exclude objects from comparison based on names. Patterns could be specified separately for each type of object. For some objects (such as triggers), qualified names might also be supported for matching both the object name (trigger) and the parent object name (table).
For example, most replication objects could be excluded using options similar to the following:
Exclude Triggers: %.ins_%, %.upd_%, %.del_%
Exclude Stored Procedures: sp_sel_%
Exclude Roles: MSmerge_%
This may not be a complete list, and might change with SQL versions, but the user could update the filters as needed.
This would allow clean compares between replicated databases and would also allow synchronization of schemas from replicated databases to non-replicated databases. This could also handle situations not involving replication.
There may be situations where a user might prefer to use different filters for difefrent database compares, so it would also be useful if these options could be stored in the project file.
(I am posting this as an alternative to a general "Exclude replicated objects and triggers" option.)
This discussion has been closed.
Comments
You can use the command line version of SQL Compare to use regular expressions to include and exclude objects (eg the Roles and the Stored Procedures). There is a specific option to ignore replication triggers. Please check out the help for more information
However I would have thought that the Stored Procs would have been set as system, so I will investigate this issue further...
Best regards
David
I now wee the batch mode options, but would also like to see the filters available to the GUI.
As for the replication store procedures, most are system proceedures, but I have one database where all of the system stored proceduers of the form sp_sel_xxxx are accompanied by user store procedures names like sp_sel_xxxx_pal, where xxxx is a matching 32 digit hex string. The server id SQL Server Developer Edition 8.00.818 (SP3) and the tables referenced by the proceedures are part of a merge publication.