WinSQL » Knowledge base

Document information

Document ID:	805
Subject:	Can I compare schema between two different types of databases?
Creation date:	7/29/09 4:23 PM
Last modified on:	12/11/18 12:03 PM

Comparing schema across DB types

WinSQL will allow you to compare schema between multiple database types, for example Informix and MS SQL Server. However, there is one important factor to keep in mind: you will most likely get a mismatch on the data type.

WinSQL considers the following parameter when matching schema for a table.

• Field names
• Field data types
• Null ability
• Default value
• Indexes
• Foreign key relationships

You will almost always get a mismatch on the data type if the two databases are of different type. This happens because the internal representation of a data type is quite different from one database to another. Consider the following scenario.

Table in Oracle	Table in MS SQL Server
CREATE TABLE ORA_TABLE(     P_ID NUMBER(*,0) NOT NULL,     DESCRIP VARCHAR2(255),     COST FLOAT(126) )	CREATE TABLE MS_SQL_TABLE(     P_ID Integer NOT NULL,     DESCRIP VARCHAR(255),     COST FLOAT )

Besides, the name of the data types being different, there is a mismatch on precision as well - meaning Oracle's NUMBER can hold up to 38 digits but SQL Server's Integer can only go up to 4294967296 (10 digits).

Work around

To avoid mismatch on data types when comparing schema between two different types of databases, you can configure the Schema Diff wizard to skip the data types during comparison. Refer to the image below.

User comments

Add a comment to this document

Do you have a helpful tip related to this document that you'd like to share with other users?

Your Name

Email

Hide my email address

Captcha

Your Tip

Submit Tip