Comparing Database Schemas, Improved Edition

The original version of this article contained this false statement: "These dbunload steps ... only work on Version 10 and 11 databases ..."

Well, not exactly false, but easily fixed so the technique will work on earlier database files: Versions 5.5, 6, 7, 8 and 9. It turns out that while the Version 11 engine can't run a Version 9 database file, that restriction does not apply to the dbunload utility. In order to facilitate database migrations, dbunload contains the ability to read earlier database files... and that ability extends to ordinary dbunload-to-a-SQL-file steps.

And that bit of magic makes it possible to compare all versions of databases. Here's the full rewrite of the original article:

---

Question: How do I compare the schemas of two SQL Anywhere databases?

Answer, from Bruce Hay at Sybase iAnywhere: Check out the new "-no" option for dbunload in SQL Anywhere 11 (unload objects in name order, no data)? It's intended for this purpose.

---

Here's how it works:

• Step 1: Run dbunload against the first SQL Anywhere database to create a text file containing CREATE TABLE and other schema definitions. The new -no option tells dbunload to unload just the schema, not the data, and do it in name order so it will be easier to compare with another text file sorted in the same order.

• Step 2: Run dbunload again, on the second SQL Anywhere database, to produce another text file.

• Step 3: Run a text comparison utility like Compare It! to show the differences between the two text files.

[ Keep reading, the new stuff is coming soon... ]

Here's why using dbunload is a good approach:

• The dbunload utility doesn't miss anything; it unloads every single schema object in the database... tables, views, procedures, triggers, everything.

• The dbunload utility is always going to be more up-to-date than any other software except the SQL Anywhere engine itself. In other words, even PowerDesigner falls behind dbunload in terms of supporting SQL Anywhere syntax for new features.

• Running dbunload on actual databases guarantees you will be comparing the current database schemas, not out-of-date information stored is separate model files.

• The text file comparison view presents differences in terms of actual database syntax, not some funky abstract logical view used by data modelling tools.

[ OK, here comes the new stuff... ]

But Wait! It's Not Quite That Easy!

How you code the "Run dbunload" parts of Steps 1 and 2 depends on what version of SQL Anywhere was used to create each database:

• If the database was created with Version 5.5, 6, 7, 8 or 9 you have to run the Version 11 copy of dbunload.exe without starting the database first. Use the -c "DBF=filespec.db;UID=xxx;PWD=yyy" connection string so that the Version 9 engine inside dbunload.exe will be used to start the database. Yes, this is magic!

• If the database was created with Version 10 or 11, you do have to start the database first, using the Version 11 engine dbeng11.exe or dbsrv11.exe, and then run the Version 11 copy of dbunload.exe with the usual client-server connection string -c "ENG=aaa;DBN=bbb;UID=xxx;PWD=yyy".

That's not just magic, it's also complicated and confusing. That's why I've created three examples you can copy and edit: Compare Version 10 and 11 databases using the second technique described above, then Version 5.5 with Version 9 using the first technique, and finally Version 5.5 with Version 11 using both techniques. These examples all use the demo databases that come with the various versions of SQL Anywhere.

Example 1: Compare Version 10 and 11 Database Schemas

This script is easily changed to work with two Version 10 databases, or two Version 11 databases.

compare_demo10_demo11.bat

REM Start V10 database via V11 engine

"%SQLANY11%\bin32\dbspawn.exe"^
  -f^
  "%SQLANY11%\bin32\dbeng11.exe"^
  -n demo10^
  "C:\Documents and Settings\All Users\Documents\SQL Anywhere 10\Samples\demo.db"

PAUSE

REM Unload V10 database

"%SQLANY11%\Bin32\dbunload.exe"^
  -c "ENG=demo10;DBN=demo;UID=dba;PWD=sql"^
  -no^
  -r c:\temp\demo10.sql

PAUSE

REM Start V11 database

"%SQLANY11%\bin32\dbspawn.exe"^
  -f^
  "%SQLANY11%\Bin32\dbeng11.exe"^
  -n demo11^
  "C:\Documents and Settings\All Users\Documents\SQL Anywhere 11\Samples\demo.db"

PAUSE

REM Unload V11 database

"%SQLANY11%\Bin32\dbunload.exe"^
  -c "ENG=demo11;DBN=demo;UID=dba;PWD=sql"^
  -no^
  -r c:\temp\demo11.sql

PAUSE

REM Compare schemas

"C:\Program Files\Compare It!\wincmp3.exe"^
  c:\temp\demo10.sql^
  c:\temp\demo11.sql 

PAUSE

Here's what Compare It! shows for one difference:



Compare It! can also produce a report in HTML format; just use File - Report... to get output like this:

Example 2: Compare Version 5.5 and 9 Database Schemas

This script is easily changed to work with two Version 9 databases, or any combination of Version 5.5 through 9.

compare_demo5_demo9.bat

REM Start and unload V5 database via V11 dbunload

"%SQLANY11%\Bin32\dbunload.exe"^
  -c "DBF=C:\Program Files\Sybase\SQL Anywhere 5.0\sademo.db;UID=dba;PWD=sql"^
  -no^
  -r c:\temp\demo5.sql

PAUSE

REM Start and unload V9 database via V11 dbunload

"%SQLANY11%\Bin32\dbunload.exe"^
  -c "DBF=C:\Program Files\Sybase\SQL Anywhere 9\asademo.db;UID=dba;PWD=sql"^
  -no^
  -r c:\temp\demo9.sql

PAUSE

REM Compare schemas

"C:\Program Files\Compare It!\wincmp3.exe"^
  c:\temp\demo5.sql^
  c:\temp\demo9.sql 

PAUSE

Here's a Compare It! screen shot:

Example 3: Compare Version 5.5 and 11 Database Schemas

This script is easily changed to compare a Version 5.5 through 9 database with a Version 10 or 11 database.

compare_demo5_demo11.bat

REM Start and unload V5 database via V11 dbunload

"%SQLANY11%\Bin32\dbunload.exe"^
  -c "DBF=C:\Program Files\Sybase\SQL Anywhere 5.0\sademo.db;UID=dba;PWD=sql"^
  -no^
  -r c:\temp\demo5.sql

PAUSE

REM Start V11 database

"%SQLANY11%\bin32\dbspawn.exe"^
  -f^
  "%SQLANY11%\Bin32\dbeng11.exe"^
  -n demo11^
  "C:\Documents and Settings\All Users\Documents\SQL Anywhere 11\Samples\demo.db"

PAUSE

REM Unload V11 database

"%SQLANY11%\Bin32\dbunload.exe"^
  -c "ENG=demo11;DBN=demo;UID=dba;PWD=sql"^
  -no^
  -r c:\temp\demo11.sql

PAUSE

"C:\Program Files\Compare It!\wincmp3.exe"^
  c:\temp\demo5.sql^
  c:\temp\demo11.sql 

PAUSE

This Compare It! screen shot shows where the schema starts to diverge; after this point, everything's different between the Version 5.5 and 11 demo databases: