SQL Anywhere 12.0.1 Sneak Peek: Compare Databases

Yes, yes, SQL Anywhere 12.0.1 is two years old so this is hardly a "Sneak Peak".

But, this feature is new to me so maybe you missed it too:

---

Support added for comparing database schemas and making them the same

You can use Sybase Central to compare two databases. The comparison generates SQL statements that you can review to determine the differences between two databases. You can execute the SQL statements to make the one database the same as the other database.

---

I like the "making them the same" part... let's see how it all works:



The "Objects" tab lists all the bits and pieces in the database, from tables down to user ids and unique constraints:

• The filter-as-you-type "Search" field is very fast,
  

• which more than makes up for the strange default sort order,
  

• plus you can change the sort order by clicking on the column headings, and
  

• clicking on a matching pair in the top frame brings up a side-by-side comparison of those objects in the bottom frame.
  

The "SQL Script" tab presents a dbunload-style script that changes all the objects in Database 1 to look (more or less) like the objects in Database 2:



Without a Search field, however, the SQL Script display is singularly useless... it's way too big to scroll through, and even if you find what you're looking for you can't do anything with it (no select, so no copy and paste).

What you CAN do, however, is press the Save As... button and then use your favorite editor (ISQL, Wordpad, whatever) to yank out the bits you want, like this:

ALTER TABLE "DBA"."rroad_group_1_property_pivot" ADD "CPU_count" integer NOT NULL COMPUTE (case when COALESCE(NumLogicalProcessorsUsed,0) > 0 then NumLogicalProcessorsUsed
when COALESCE(NumProcessorsAvail,0) > 0 then NumProcessorsAvail
else 1
end)
go

ALTER TABLE "DBA"."rroad_group_1_property_pivot" ADD "autodropped_connection_count" integer NOT NULL DEFAULT 0
go

CREATE INDEX "ix_number_DESC_id_lost" ON "DBA"."rroad_group_1_property_pivot"
    ( "sample_set_number" DESC,"sampling_id","sample_lost" )
go

CREATE INDEX "ix_id_lost" ON "DBA"."rroad_group_1_property_pivot"
    ( "sampling_id","sample_lost" )
go

There are some restrictions, like it only works on database files created with SQL Anywhere 10 or later, plus those databases have to be running on SQL Anywhere 12 engines, and it won't change the order of columns in a table:

ALTER TABLE "DBA"."rroad_odbc_dsn" DROP PRIMARY KEY
go

ALTER TABLE "DBA"."rroad_odbc_dsn" ADD PRIMARY KEY ("odbc_dsn" ASC,"session_id" ASC)
go

// Can't reorder columns for table "DBA"."rroad_odbc_dsn"
// ("ALTER TABLE ... ADD column-name ... " adds columns to end of table only)
// Database 'f - DBA' (database 1):
// CREATE TABLE "DBA"."rroad_odbc_dsn" (
//     "session_id"                     varchar(36) NOT NULL
//    ,"odbc_dsn"                       varchar(255) NOT NULL
//    ,PRIMARY KEY ("session_id" ASC,"odbc_dsn" ASC) 
// )
// Database 'f - DBA' (database 2):
// CREATE TABLE "DBA"."rroad_odbc_dsn" (
//     "odbc_dsn"                       varchar(255) NOT NULL
//    ,"session_id"                     varchar(36) NOT NULL
//    ,PRIMARY KEY ("odbc_dsn" ASC,"session_id" ASC) 
// )

Other limitations are described here, including some that might involve loss of data.

---

In SQL Anywhere 16, Compare Databases is pretty much the same except...

• it's now called "Compare Database Schemas..." to reduce expectations about the data,
  

• the password is no longer blanked out (grrr!) every time you open the Connect window, and
  

• it now insists you start both databases using SQL Anywhere 16 rather than version 12.
  

As features go, it's a good start. At the very least, it will be immediately useful for generating those funky ALTER statements needed to deploy changes that were initially coded by DROP and CREATE.

And for double-checking "what's different" between old and new versions of a database... especially when you need to see all the nooks and crannies in the schema.

For daily use, however, not so much... not until it gets some more ease-of-use enhancements, like the ability to search and select in the SQL Script pane, and to change the order of the scripts.

Yes, the generated order of all those ALTER statements is important if you're going to run the whole thing at once, but...

...that's an unlikely use for it.

---