Compare SQL Server Tables

This workflow compares the user tables that are common to two SQL Server databases, and produces HTML reports showing the comparison results. The workflow was tested using databases from a SQL Server instance on which the LiveCompare workspace was registered (the local SQL Server instance). If you are using a local SQL Server, it may be necessary to configure this server to support Data Access. This can be done by running the following in Query Analyzer:

 

sp_serveroption @server = '<server name>', @optname = 'DATA ACCESS', @optvalue = 'TRUE'

 

If you wish to compare databases that are registered on a remote SQL Server, you will need to add it to the local SQL Server instance as a linked server. This can be done as follows:

 

exec sp_addlinkedserver '<remote server>',

N'SQL Server'

 

To prepare the workflow template, import it into a workspace and modify the Database Details Parameter table so that it contains the server and database name of each of the databases to be compared. If you need to refer to a named SQL Server instance, enclose it in square brackets like this: [myserver\sqlexpress].

 

Before closing the LiveCompare ‘Table’ dialog, make sure the ‘Force Upper Case’ checkbox is unchecked.

 

To exclude table names from the parameter, edit the ‘Statements’ property for the Filter Tables Execute SQL action.

 

Before comparing each table, the workflow sets one or more table columns to be key fields. The columns to set for each table are specified in the Table Keys Parameter table. This table should be modified so that it includes a comma-separated lists of fields for each table that is to be compared. Before closing the LiveCompare ‘Table’ dialog, make sure the ‘Force Upper Case’ checkbox is unchecked.

 

To run the workflow, press F5 or click the green arrow. When the workflow has finished, the ‘Reports’ folder in the LiveCompare hierarchy should contain an HTML report for each table that was compared. Choose ‘Reload Children’ from the Report folder’s context menu to list the reports. Each report name is time-stamped and begins with the name of the compared table.