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:7/29/09 4:24 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
  • Nullability
  • 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 OracleTable 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.







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? Please add it below. Your name and tip will appear at the end of the document text.
Your name:
Your email:
Hide my email address
Verification code:
Enter the verification code you see above more submitting your tip
Tip:Please limit tips to 1000 characters

Navigation

Social Media

Powered by 10MinutesWeb.com