Comparing Database Schemas

[ An improved version of this article may be found here. It shows how to use this technique on all database versions 5.5 through 11. ]

---

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.
  
  These dbunload steps only work with the SQL Anywhere Version 11 copy of dbunload.exe, and they only work on Version 10 and 11 databases running on the Version 11 database engine.

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

Here's an example of Windows commands that compare the Version 10 and 11 demo databases:

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

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

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

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



Compare It! can also produce a report in HTML format:



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.