http://www.sqlservercentral.com/blogs/steve_jones/2012/08/09/excluding-schemas-in-sql-compare/

Printed 2014/10/24 12:33PM

Excluding Schemas in SQL Compare

2012/08/09

I saw this question posted the other day and thought it was a great idea.

sqlcompare1

I haven’t been a big schema user in the past, but I see the value of excluding some schemas. CDC for sure, but I know some people have an ETL schema, which is much nicer than having Staging_Customers. I think the exact same structure in ETL.Customers moving to dbo.Customers is a good idea.

sql-compare-logoI sent a note to the PM for SQL Compare, since I thought this might be one to bump up on the list of things that might make it into Compare 11. We’re on Compare 10 now, which is amazing. I remember Andy Warren talking about how cool this product was back in 2002 when it was just SQL Compare.

The PM told me we already do this, so I started digging in. Sure enough, we can do it.

I can do a quick compare of two databases on a test instance. I’ll pick a new project and select two similar, but slightly different dbs.

sqlcompare2

Once the comparison runs, you see there are three objects that are in one database, but not the other.

sqlcompare3

This shows me I have a schema (ETL) and a table (ETL.Contact) in that schema, as well as a separate table in another schema (Person.Contact2). I want to exclude the ETL stuff, so what can I do?

There’s a Filter Rules button, that shows the filters on the left side by default. It contains all the types of objects, which is what I normally use it for, excluding some types.

sqlcompare4

However I can click the “Edit Filter Rules” button, and I get a dialog, where I can enter various conditions.

sqlcompare5

Here I’ve excluded (upper left) objects if the Schema begins with ETL. I can click OK and then re-run the comparison. I’ll then get:

sqlcompare7

Better, but I still have the ETL schema, which I may not want to move over.

As a visual cue (if you look for it), I can see I have a rule set in the Filter pane on the left. It’s subtle, but it’s in a maroon-like color.

sqlcompare6

If I go back, I can actually add a second condition here and change my operators to prevent mismatches with similar names, by using an “Equals” operator.

sqlcompare8

Now I get what I wanted. Note that this took effect immediately and I didn’t have to run the comparison again. SQL Compare finds all the changes and then filters them.

sqlcompare9

If this is something I do regularly, like move from Development to QA, I can save the project:

sqlcompare10

The next time I need to run this, instead of using the New Project dialog, I can just open this one and run the comparison.


Filed under: Blog Tagged: Red Gate, SQL Compare, syndicated
Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.