Converting a database schema to match another

You can use Sybase Central to compare the schema of two databases. The output of the comparison is in the form of SQL statements that you can run on Database 1 to make it the same as Database 2.

Prerequisites

The SQL script that is generated contains the statements necessary to convert the schema of Database 1 to match the schema of Database 2. It is recommended that you back up Database 1 before running the script, and that you ensure there are no other connections to the database while the script is running.

Context and remarks

The following operations cannot be performed on Database 1 using this feature, and therefore are not included in the list of SQL statements generated during the comparison. When one of these operations is required, a notification describing the operation appears at the beginning of the list of SQL statements that is generated.

  • Altering the file name for a dbspace

  • Altering a domain

  • Altering the read-only setting for a remote server

  • Altering the dbspace for a table

  • Altering the COMMIT action and/or SHARE BY ALL setting for a global temporary table

  • Altering the order of columns in a table or global temporary table

  • Altering the synchronization type for a publication

Note

When comparing databases, objects are not matched by object ID, but by name, name and owner, or a combination of names and owners. Any objects with names in Database 1 that do not appear in Database 2, including renamed tables and columns, are dropped and recreated, which may result in the loss of data.

 Convert a database schema to match another

  1. In Sybase Central, use the SQL Anywhere 12 plug-in to connect to the two databases you want to compare.

  2. Click Tools » SQL Anywhere 12 » Compare Databases.

    The Compare Databases window appears.

  3. Ensure that the database you want to convert the schema of is specified in the Database 1 field, and the database you want to convert the schema to is specified in the Database 2 field.

  4. Click Compare.

    The Objects tab displays the list of objects in the databases. The SQL Script tab displays the generated SQL statements.

  5. Click an item on the Objects tab to view the differences in the SQL statements for the object. A red background indicates SQL for objects that exist in Database 1 only, a green background for objects that exist in Database 2 only, and a blue background for objects that exist in both databases.

  6. Click Open In Interactive SQL on the SQL Script tab to open Interactive SQL with the statements pasted in the SQL Statements pane. You can make further edits as required and execute the statements. Executing the SQL statements makes Database 1 the same as Database 2.

Results

The schema of Database 1 is converted to match the schema of Database 2.

Next

None.

 See also
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1