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

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.

I 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.

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

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.

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

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:

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.

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.

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.

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

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