SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Excluding Schemas in SQL Compare

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.

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.


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

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...