To start a comparison between select tables and/or indexed views click on the IDERA SQL Data Compare Compare Tables/Views button on the Home tab of the ribbon. On the Object Compare dialog window that appears you can first select the servers and databases on which the objects you wish to compare reside. In case there is a large number of objects in those databases you may wish to specify a pattern for the name of the objects you are looking for (ex. all tables that start with a). That will make it easier to find those objects.
- Comparing Views. IDERA SQL Data Compare, in addition to tables, has the ability to compare views as well. The option Compare and Synchronize Views determines whether views should be included in the comparison and synchronization. However, use this option with caution. There are several limitations that SQL Server imposes on views:
- Not all views are updatable.
- If a view has indexes, Data Compare will select one in the same order as table's indexes; otherwise you must define a custom index in order to be able to compare the view.
- Since SQL Server does not support SET IDENTITY_INSERT ON|OFF on views, the insert statements might fail if one of the view’s underlying tables contains an identity column.
- Data Compare cannot synchronize views that contain large binary fields such as varbinary(max) and image, or views with large text field such as varchar(max), nvarchar(max), text and ntext.
Once you have made your selections above you can click on the Read Database Objects link. IDERA SQL Data Compare will read the schemas of the two databases and populate the two left bottom windows with the list of tables / views available.
Now you can map those objects one at a time irrespective of the object names. Select an object on the left, then select an object on the right and click the left to right arrow button to map those two objects.
The SQL Data Compare Comparison Options affect the ability to compare objects. For example, if you clear the Compare and synchronize tables option, then you cannot compare the tables but can only view and map them. The same holds true for indexed views which by default are not included in the comparison.
Total compare from data to schema with IDERA SQL Comparison Toolset. Learn more > >