How to compare two SQL scripts/objects and export comparison results

Applies to
ApexSQL Compare

Summary
This article explains how to compare two SQL scripts/objects and export comparison results into a HTML report.

Description

ApexSQL Compare is a tool that can compare different type of files, SQL objects and folders.

Once run, the New project window will be shown and the Files comparison type should be chosen:

After a click on the Files comparison type, the Data source tab will be shown. Here, a database object or a file can be selected for the comparison. In the following example, existing database object will be compared against a newly created SQL script for the same object:

Quick tip icon

Quick tip:

ApexSQL Compare currently supports syntax highlighting for 9 languages:

  1. C#
  2. C++
  3. HTML
  4. JavaScript
  5. PostgreSQL
  6. Python
  7. T-SQL
  8. Visual Basic
  9. XML

More may have been added after this article was published

Under the Options tab, the desired Comparison mode and several Ignore options for the comparison process, can be set based on preferences:

By default, the Line comparison mode is used and none of the Ignore options are checked, but in this example, the Character comparison mode is used to better highlight any differences in combination to ignore comments and collations.

Quick tip icon

Quick tip:

ApexSQL Compare supports three different comparison modes:

  1. Character – compares scripts character by character and highlights differences on a character level
  2. Line – compares scripts line by line and highlights differences on a line level
  3. Block – compares scripts block by block and highlights differences on a block level

Once the data sources are set up, along with all desired options, in the bottom-right corner of the New project window, click the Compare button to initiate the comparison process.

Upon the finished comparison process, the compared SQL object and script are shown in two panels, along with all highlighted differences:

As the Character comparison mode was used, different rows along with all character differences are properly highlighted, as shown in the screenshot above.

Also, the amount of different lines can be viewed at the bottom of the main application window, in the Status bar:

If you want to locate a specific word/change, switch to the View tab in the main window, and click the Find button:

This button will show the Find panel, where you can select a panel, in which searched should be conducted, and enter a desired word for search:

Before exporting comparison results into an HTML file, a few options can be set to customize reports. Click the Options window from the Home tab and switch to the Output tab:

In the first group of options, a file location and file name template can be set and used as defaults. In addition, a separate CSS file can be created next to the HTML file for additional editing, to change HTML file colors, fonts, etc.

The optional output elements group allows to add author name, date and time, and legal information to HTML report header.

If all options are set, click the Export button from the Home tab, and the comparison results will be exported to the HTML file. Upon generating it, the application will show a prompt window whether to open it now or not. If the Yes button is clicked, the HTML report will be opened in default browser for reviewing:

FAQs

Q: Can I change the difference highlighting colors?

A: Yes. You can change difference highlighting colors in three ways:

  1. Right-click on the right/left panel and click the Difference highlights option from the context menu
  2. Go to the View tab in the main application window and click the Difference highlights button
  3. Click the Options button, from the Home tab of the main application window, and under the Difference highlights section, you can change default colors

Q: Is there a word wrap option?

A: Yes. You can toggle the Word wrap option in three ways: from the View tab, right-click context menu in both panels, and in the Options window, under the General tab.

Q: Can I compare SQL objects from SSMS

A: Yes. ApexSQL Compare offers integration for SSMS and Visual Studio, so you can right-click a SQL object in Object Explorer, select the Compare scripts in the right-click context menu, then Set as left/right, and the New comparison query window will be shown.

Q: Can compare two files with a right-click?

A: Yes. ApexSQL Compare also offers Windows Explorer integration and you can right-click a file, select Compare files -> Set as left, then right-click the other file -> Set as right, and the comparison process will be initiated.

Q: Can I synchronize changes between files?

A: No, but you can create a merge script with a click on the Merge button from the Home tab. That will open the Merge view panel and check-boxes will be shown next to each different line/block and it will allow you to customize the merge script.