Selecting Tables and Views

When you create a comparison project and you have selected your data sources, you specify the tables, views, and columns that SQL Data Compare will compare. You enter this information using the Tables & Views tab on the Project Configuration dialog box.

The Tables & Views tab enables you to:

select the comparison key for each table or view
select the tables and views that will be compared
select the columns that will be compared
filter the rows that will be compared

SQL Data Compare lists the tables and views in your databases; tables and views with identical or similar names are displayed side-by-side. Note that views are listed only if you select the project option Include views, and you are not using a backup as a data source.

You can change the order in which the tables and views are listed by clicking the appropriate column header. To sort by multiple columns, click a column header, then hold down SHIFT and click another column header.

If you are setting up a new project and SQL Data Compare is unable to map a table or view, you can map the tables and views manually to compare these objects.

If you are editing an existing project and the structure of the database has changed since you last ran the project, a warning symbol is shown to indicate that those changes affect your project configuration; see Mapping Errors for more information.

Selecting the comparison key

To match rows in the two data sources, SQL Data Compare requires a comparison key for each table or view.

SQL Data Compare automatically selects a comparison key, if your:

tables contain a matching primary key, unique index, or unique constraint
views contain a matching unique, clustered index

If SQL Data Compare is unable to identify a suitable comparison key for a table or view, Not Set is shown in the Comparison Key box. You set the comparison key by clicking the appropriate Comparison Key box. A dialog box is displayed, for example:

Select the columns in the table or view that will comprise the comparison key by using the check boxes.

Note the following:

A comparison key cannot include columns whose data type is image, ntext, nvarchar(max), sql_variant, text, varbinary(max), varchar(max), or xml.
You cannot specify custom comparison keys if you are using a backup as a data source; however, you can select an alternative unique index or unique constraint.

Selecting the tables and views

You select the tables and views that you want to compare by selecting or clearing the appropriate check boxes in the Compare box. Note that you cannot compare views if you are using a backup as a data source. To compare all tables and views, click All; to clear all of the check boxes, click None.

By default, the first time that you run a project all tables and views with identical or similar names are selected for comparison. If the structure of the databases that you are comparing has changed while you are working on a comparison project, click Refresh to update the Tables & Views tab. For example, if a table has been added to the database, click Refresh so that you can include the new table in the comparison.

If a table or view has been added to a database since you last ran the project, SQL Data Compare does not select the table or view by default. For troubleshooting information about table mappings, see Mapping Errors.

Finding a table or view

To locate tables or views, type the search text in the Filter box. As you type each character, only tables or views with a name (or owner) that matches the search text are displayed. Click the arrow button to select a recent search.

If there are no matches, the Filter box changes color.

To clear the Find box click the button.

Note that hidden tables or views will still be compared, if you have selected them.

The search is not case-sensitive.

Selecting the columns

SQL Data Compare displays the number of columns that will be compared for each table or view. By default, the first time that you run a comparison project all columns with identical or similar names are selected for comparison.

If a column has been added to a table while you are working on a comparison project, click Refresh so that the new column is included in your project.

You can select which columns to compare by clicking the appropriate Columns in Comparison box. In the dialog box, select or clear the check boxes as required.

You cannot exclude any columns that are used for the comparison key (indicated by ).

A warning symbol is shown when columns you are comparing have:

incompatible data types
For information about the data types that SQL Data Compare can compare, see Troubleshooting.
column names that do not match
For information about mapping columns, see Mapping Data Sources.

Filtering the rows

For each table or view, you can restrict the rows that SQL Data Compare will include in the comparison by specifying a WHERE clause. To do this, select the table or view and click WHERE Clause; to apply the same WHERE clause to multiple tables, use SHIFT+Click or CTRL+Click. Alternatively, you can right-click the tables or views and click Open WHERE clause editor.

Note that you cannot filter rows if you are using a backup as a data source.

On the WHERE Clause Editor dialog box, type the WHERE clause in the box.

If you want to apply a different WHERE clause for each data source, clear the Use the same WHERE clause for both data sources check box and type the WHERE clause in the box for the database on the right. For an example, see Applying Conditions to the Comparison.

You can type any valid Transact-SQL WHERE clause. For example, if a table has columns ID, FirstName, and LastName, you may want to compare only rows where LastName is Smith. To do this, type the following in the box:

LastName='Smith'

Click OK. In the Tables & Views tab, indicates that the table or view will be filtered using a WHERE clause.