Comparison of database schemas

Database administrators and other IT professionals often need to compare the structures of similar databases. DatabaseSpy 2010 adds a new visual database schema comparison feature to accelerate this task.

Database schemas can be compared within the same database type, or across database types. For example, two SQL Server database structures can be compared, or a database schema in SQL Server can be compared with an Oracle database or with any other database type supported by DatabaseSpy 2010.

DatabaseSpy 2010 automatically maps corresponding components within the compared database schema structures. When components are not named identically, the user can map components manually.

In the example shown above, the selected Employee tables are not equal. The datatypes of the title columns are highlighted to indicate they are not identical, the EyeColor column in the table on the right does not exist on the left, and so on.

DatabaseSpy 2010 allows users to create change scripts to merge differences in database schemas in either direction.

Database schema comparison settings can be saved in a file and added to a DatabaseSpy project for quick retrieval and reuse.

Conversion of database structures between different database types

Migrating an existing database from one database type to another, for instance from MySQL to SQL Server, can be a difficult and time-consuming effort due to differences in SQL dialects and datatype definitions between database vendors. DatabaseSpy 2010 speeds this job with new database structure conversion functionality.

Users of DatabaseSpy 2010 can open multiple Design Editor windows for different database types and simply drag existing tables from one database to another. In the example below, a table has been copied from MySQL to SQL Server (each database type is identified in the information bar at the bottom of the Design Editor window).

Dragging the table to a new database instructs DatabaseSpy 2010 to create a Database Structure Change Script that contains SQL statements to create the table in the new database SQL dialect. The Database Structure Change Script can be executed immediately or saved as a SQL file for execution later, perhaps even on a different database server.

Alternatively, one or more tables can be dragged from the DatabaseSpy 2010 Online Browser directly to the Design Editor window for a different database type. In this example, multiple MySQL tables have been added to a SQL Server Design Editor:

The Database Structure Change Script is created immediately and contains SQL statements to create all three tables.

Display of additional calculated information for selected results

DatabaseSpy 2010 now allows users to calculate and display additional information about selected data displayed in the Results window tabular display of data retrieved by SQL statements. Users can select any range, or even a group of non-contiguous cells within the Results window and choose to display:

• The count of selected cells
• The total number of selected cells containing numerical data
• The average of all selected numerical values
• The sum of all selected numerical values
• The minimum selected numerical value
• The maximum selected numerical value

The calculated information is displayed in the status bar below the Results window, and the information is immediately and automatically updated as new sets of results are selected.

Selection of displayed information is controlled via a new DatabaseSpy 2010 right-click context menu available from the status bar.

Display of this information will help users explore and find quick answers in stored data without writing and debugging complicated SQL statements.

Result grid printing

DatabaseSpy 2010 allows users to print Results tables directly. Users may print either the entire Results table, or a selected range. A print preview is also available with enlarge / reduce functionality.