Excluding columns from the data comparison

BillGraziano

I'm trying to compare data in two tables but I want to exclude two columns -- the last modified date and the username of the person that modified it. (I'm using version 3.3.8.304 which I just downloaded.)

I looked online and saw Screenshot 2 on your web site:



That shows a list of columns to include/exclude in the comparison. However my dialog doesn't have the "Columns to Compare" column. There's no place to exclude the columns.

I also looked in the help file and found this text:

I have a column I want to exclude from the comparison. Can I do this?

Yes. You can use the project dialog to choose the columns to include in the comparison.

Where's the "project dialog"? Is this the wizard?

How do I exclude certain columns from the comparison?

-Bill

Brian Donahue

Hi Bill,

Thanks for your post. Maybe the terminology in the help is a bit confusing. When you start the software, you're presented with the 'new project wizard', but it's exactly the same process that you go through to edit an open project. So say you've already done a data comparison -- then you click the 'Edit Project' icon on the toolbar and see the exact same thing as the new project wizard!

The second screen of the project settings is where you can choose the columns. If you click on the cell in the row of the table you want to compare, a dropdown button should appear so you can select/de-dselect columns.

If it doesn't happen, it may be a pecularity of this table. You might want to send us a copy of the table script, either post it here or send it to support@red-gate.com.

Thanks!

BillGraziano

I finally found that final column. This dialog box is ... odd. First, all the columns together are wider than the screen is and I can't resize the dialog. Can you allow the dialog to resize? Or maybe I should ask why does the application prevent me from resizing this dialog box so I can use your application easier?

Second, when the PK column is on the screen it fills the entire grid. If I click on the scroll bar to the right of the current area it won't scroll to the right and display the column that allows me to exclude columns for comparison. I have to grab the scroll bar and manually drag it to the right to see the column. Very annoying. That's why I didn't think the column existed in the first place.

Also, it is very difficult to resize the PK colmn to make it smaller so I can actually scroll around. It took me a long time to shrink all the columns down so I could see the table name and the drop down box to exclude columns at the same time. Can you test your app with some really long table name and really, really long primary key names.

Finally all our tables have a column for the last modified date and who modified the the row. They are named the same in every table. It would be GREAT if I could automatically exclude those two columns from every comparison. As it is I'm manually working my way through the 40 or so tables we need to compare and unchecking those column. That works well for this small rollout but I'm not excited about it for our next rollout which will be 150+ code tables. I looked into manually editing the project file but that didn't look much better.

Thanks!

Brian Donahue

Hi Bill,

Perhaps it would save about a thousand words if you sent a screenshot of your GUI woes to support@red-gate.com. This would help us to try to figure out what would be the best way to modify the GUI.

The latest SQL Bundle (3.3.8) includes command-line utilities (sqldatacompare.exe) that allow you to include tables or columns based on a regular expression. These may suit you better is you want to conditionally ignore columns or tables.

In the GUI version you can save a project, and that way you can compare databases using the saved settings so that you do not need to check and un-check columns and tables every time you use the software.

Cory.Foy@mobilehwy.com

I just downloaded the latest bundle and this:

/columns:.*:.*

doesn't work for the columns. It succesfully matches the first table, but doesn't recgonize the column.

This would solve my issue because we have a similar problem where a timestamped column needs to be excluded, but I don't want to have to list all 350 columns in the table that has it. (It's a joy working with legacy data!)

So how can I apply regex for the colum names in the command line version?

Brian Donahue

Hello Cory,

I think that the only way it works is to list the columns in a comma-separated list. For instance I used this to compare a selection of tables in the DCBug and Client tables:

C:\Program Files\Red Gate SQL Bundle>sqldatacompare /db1:datachooserec1 /db2:datachooserec2 /include:Table:DCBug /include:table:Client /exclude:view /columns:Client:CheckedOut,ID /columns:DCBug:stuff