SQL SERVER – SQL Server Schema Compare Tool

In this article, I’ll show how to use ApexSQL Diff, a SQL Server Schema Compare Tool. You can download it here, install, and play along while reading this blog post.

But first, let’s say something about this database software.

ApexSQL Diff is a SQL Server tool that is used to analyze and compare object schemas between two SQL databases, and also to synchronize particular, or all differences between them. It is used for SQL Server schema compare and synchronize the following data types:

• Databases
• Backups
• Script folders
• Snapshots
• Source control

It can also be used to repair or roll back changes from backup files, which can be very useful if you made some unwanted changes.

The new ApexSQL Diff 2014 comes along with SQL Server 2014 support and the new user interface style. In the free, Community edition, the user can compare and synchronize SQL Server Express and Windows Azure SQL databases.

Right after the program is started, there is a Project management dialog, where you create a new project, or open the previously saved one. If you select New, the New Project dialog opens.

The first option in this dialog window is for selecting source and destination data source types. You can select data types from a list: Backup, Database, Script Folder, Snapshot, and Source Control. The Project dialog offers an option to revert a source and a destination, and to clone information between them. There is also an option to export data source to a snapshot file, a script folder, or to source control.

When you select Database as data source type, the information you need to provide are server name, login information, and select a database from the drop down list of all available databases on the specified server.

If you select Backup or Snapshot as a data source type, then you need to provide a path to a file.

By choosing the Script folder option, apart from the script folder location, you have an option to Configure mappings, to set a default Collation, and a SQL Server version.

When you select the Source control option as a data source type, there are Source control settings which are in wizard form, and will guide you through setting up Source control type, login information, and configuring mapping. There are also options to set the Default collation and the SQL Server version

Apart from the regular options, if you click on the Show advanced tabs button, three more tabs will appear: Schema mapping, Object filter, and Additional scripts.

In the Schema mapping tab you can specify how owners/schemas are handled during the SQL Server schema compareprocess, by custom mapping schemas in a source and a destination database. You can also export/import mappings to save/load previously defined/exported mappings. Owners/schemas with the same name will be automatically mapped to each other.

To select specific objects for a database comparison, you can use the Object filter option. Here, all object types are shown in a detailed list, so you can choose the ones you want to include in the SQL Server schema compare process.

By choosing each object, it will appear in the first column of the Object filter tab, and clicking on it, a new window will show all objects of that type, which you may choose to include/exclude from the SQL Server schema compare process.

Lastbutnot least, the Additional scripts tab allows you to use scripts before and/or after the synchronization script is run. You can choose not to use it, to use an embedded script, or to use a saved script.

At the end, you can save the selections to a project and use it again, and save time to make the selections again. To run a saved project, select it from the Project management dialog and it will load all previously saved settings.

When everything is set up, click the Compare button. The comparison results will be shown in the Main grid. There are several columns in the Main grid: Object type, Source schema, Source object, Destination schema, and Destination object. Any of those columns can be used for sorting the results, just by clicking on them.

In the View menu, there is the Multi column sort option for more advanced sorting. It allows you more sorting options and you may select up to four columns by which the results are going to be sorted.

There are several other buttons, in the View menu, which can be used to adjust the way the results are shown. You can set an alignment to left, or centered. There are buttons for grouping the comparison results. The results can be grouped by a different type (not equal, missing, added, or equal), by an object type (tables, views, procedures, etc.), or no grouping at all. There are two more buttons, Collapse group and Expand group, which allow the user to automatically collapse of expand all selected groups in the Result grid. The last button for adjusting the way the results are shown is Auto size columns, which is used to auto adjust the width of the columns.

When you select an object from the Main grid, differences between the object scripts can be seen in the Script difference pane. This pane is divided on two sides. The source script is shown on the left side, and the destination script is shown on the right side. Both scripts are scrolled together, regardless on which side you’re scrolling, so you can easily see the differences in code between them. Different lines of the scripts are highlighted, so they stand out from the rest of code, which makes them easier to find.

You can access to several options in the Script difference pane from the context menu. By default, the Enable syntax option is selected. It shows all SQL reserved words in a different color. The View whitespace option shows all formatting symbols used in the code, a new line, tab, etc. The Line numbers option is used to quickly find a certain line of code. Finally, there is an option to select different font sizes for displaying code in this pane. The default setting is Medium, but it can be changed to Large or Small.

On the far left side of the pane, there is a bar (marked red in the picture) with blue lines that represent positions in the script where the differences are.

If you want to limit the SQL Server schema compare to the specific objects in a database, you can use the Object filter feature. Select the wanted SQL object, and an icon of the Object filter will appear. By clicking on the icon, a new dialog opens, and you can select a custom filter criteria for that object. You can include/exclude some of the objects in the SQL Server schema compare. With the Filter editor option, you have the ability to specify custom conditions to include the relevant tables:

After you have selected all desired objects, you may start the synchronization process with the click on the Synchronize button. The Synchronization wizard will appear with the first step of synchronization direction, where the source and destination data sources are presented, with an option to reverse the direction of the synchronization.

The next step will show you all dependencies for the selected objects, with an option to include or exclude them from the synchronization.

In the Summary and warnings window you’ll see all actions that are going to be performed. You may group them by the execution order, by an object, or by the action type.

In the final step, you are asked to select the output options. You may choose to Create synchronization script, or to Synchronize now.

If you select the Create synchronization script option, there are options to save the script to a file, where you can specify a folder and a file name, and to open the script in an editor. You can use the internal script editor, which is a default option, or assign another one.

Choosing the Synchronize now option, will also give you the option to save the synchronization script to a file, and to create a backup of a database before the synchronization. For creating a backup, you can choose the default directory, or select some other folder.

After the script is created and reviewed, you may execute it.

ApexSQL Diff provides several options for creating reports.

Under the Home tab, there is an Export button, where you will find all options for creating reports. You can export comparison results as an HTML plain report, an HTML report, and an XML structure report.

An HTML plain report is ideal for printing, while a HTML report is designed for electronic browsing since it contains toggles that expand and collapse groups.

After you select one of these reports, it will be generated and presented.

Reference: Pinal Dave (https://blog.sqlauthority.com)