Comparing Database Schemas

www.altova.com Print this Topic Previous Page Up One Level Next page

Home > 

Comparing Database Schemas

A database schema comparison enables you to compare objects of two different database schemas, in terms of their structure or size. Database objects that can be compared include tables, columns, views, functions, and stored procedures. Comparison works differently for tables, as opposed to views, functions, or stored procedures. Namely, in case of tables, the comparison results report the structural differences (such as different columns, constraints, data types, and so on). In case of views, functions, and stored procedures, the comparison results inform you if the size of the object is the same or different in database A (left-side of comparison) as compared to database B (right-side of comparison).

 

Note:Line-by-line text comparison of database object definitions is not supported. To compare two object definitions (for example, database views) line-by-line, first save the definitions to files and then open both files in a differencing application such as DiffDog.

 

To start a database schema comparison, click the Schema Comparison ic_schema-compare toolbar button. Two database components are displayed side by side (a "left" component and a "right" component). Before running the actual comparison, you need to assign a data source to each of the left and right components. Then you can select for comparison either all objects of a schema, or specific schema objects. To change the objects included in comparison, click the Browse diff_browse_button button on the right or left component, and select or clear the required check boxes. For more information, see Running a Database Schema Comparison.

dbdiff_schema_comparison_sample

Sample database schema comparison

The sample database schema comparison above illustrates the result of comparing a stored procedure, a table, and a view between two SQL Server databases, "ZooDB" and "ZooDBTarget". The table definitions are equal in both the source and target schema (which is indicated by the ic_equal sign), while the stored procedure and view definition is different (which is indicated by the ic_diffs sign).

 

A database schema comparison can be saved as a file. Database schema comparison files have the .dbsdif extension, and can also be opened with DatabaseSpy and added to DatabaseSpy projects. A comparison file stores the connection details of the database(s) involved in comparison, the database objects selected for comparison, as well as any configuration options you have defined in the "Properties" window (see also Database Schema Comparison Files).

 

Various database comparison options are configurable. For example, you can have DatabaseSpy automatically match the tables or columns to be compared (by name, data type, or both), or perform the mapping manually. The comparison options can be adjusted from Tools | Options | Data Compare.

 

After running a database schema comparison, you can generate a merge script to update either the left or the right database. You can review and edit the merge script before actually executing it. It is also possible to select specific objects to be included in the merge script (for example, one or several columns of a table). For more information, see Merging Database Schemas.

 

Note:The merge script synchronizes only differences resulting after comparing structure of tables (this includes changes to columns, keys, or constraints). Merging differences between other object types (such as stored procedures, functions, and views) is not supported.

 


© 2019 Altova GmbH