Schema comparison tool for ASA

Is there a Sybase tool for schema comparison (ASA v9)? There are a few commercial ones but I'm looking for a Sybase product or an open-source one.

Update: Below I sum up the suggested options and what I have tried so far.

Use PowerDesigner
Query the System catalog, output the results to text files and compare these using WinDiff
Use dbunload -n (or better, dbunload -no ) to output database schema to text files and compare these using WinDiff. This is the way I chose eventually.

My tests:

PowerDesigner came free with SQL Anywhere (no license problem). I could reverse-engineer and compare two databases without looking up the documentation. However I don't know if this can be automated conveniently. I actually didn't have time to search for possible command-line parameters of PowerDesigner that can refresh the models from database and bring up a comparison window. I doubt it exists, but please let me know if there's an agreeable way.
I think this is the most scalable approach. Also it can be automated easily. The downside is you have to make sure you don't skip any database object. Also there are multiple files to track if you put these in source control.
This is a straightforward solution for small to medium-sized databases, and also automatable. Having a single file that contains all the schema information is really convenient. It's true there might be some "artifacts", but they only come up rarely since I don't make have heavy use of procedures/triggers. Beware, dbunload -no doesn't order the objects in ASA v9. I wrote a small program to sort it.

comparison asa-9 schema

asked 31 May '11, 12:30

henginy
406●15●18●27
accept rate: 0%

edited 15 Jun '11, 02:56

Comment Text Removed

FYI the Version 11 dbunload -no works on Version 5.5 through 9 databases; see Example 2 in http://sqlanywhere.blogspot.com/2009/10/comparing-database-schemas-improved.html

(15 Jun '11, 07:42) Breck Carter

flat view

3 Answers:

active answers oldest answers newest answers popular answers

Probably the simplest thing to do is to do a dbunload -n on both databases and then use diff to compare them.

permanent link

answered 31 May '11, 14:00

Phil Mitchell
1.9k●18●31
accept rate: 27%

I would suggest to use "dbunload -no" to get an unload script where each object type is ordered by name, not by creation time - that will usually be more suitable for a database comparison.

(01 Jun '11, 08:28) Volker Barth

And besides that, when using DBUNLOAD I have often noticed differences in procedures/triggers/events and the like just because of different line breaks in the objects's definitions - at least when comparing between different database versions. (Note: The "preserved source" was still identical.)

Therefore this approach might show several artifacts in addition to "real differences", and it will take some time to tell which is which:(

(03 Jun '11, 08:21) Volker Barth

flat view

If you're looking for a GUI tool, try PowerDesigner. It's included with SQL Anywhere.

When you download the SQL Anywhere Developer Edition (http://www.sybase.com/detail?id=1016644) you can also download the eval copy of PowerDesigner.

Here's more information including screen shots: http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc38093.1530/doc/html/rad1232024772009.html.

permanent link

answered 31 May '11, 15:59

José Ramos
1.0k●5●15●24
accept rate: 30%

flat view

Another approach would be to query the system catalog on both databases and output the result to a text file and compare that.

We do that routinely to assure our update scripts work as expected, dump them to different folders and compare them by WinDiff.

That way you can decide if you

just want to check whether the according objects exist in both databases or
you need to know if they are exactly the same.

I add a few of those queries just to give you a hint - if that approach is generally useful to you, you will obviously adapt some of those queries.

FWIW: The schema creator is dbo here (UID = 3).

-- list all columns
select st.table_name, sc.column_name, sc."check", sc."default", sc.user_type
from syscolumn sc key join systable st
-- possibly limit to particular tables
where table_name in ('')
order by table_name, column_id;
output to '.\ColumnList.txt';

-- list all tables, procs, triggers and rights of the relevant creator
select table_name, table_type,
   (select count(*) from syscolumn sc where sc.table_id = ST.table_id) nCols
from systable ST
where creator = 3 and table_name not like 'sys%'
order by table_name;
output to '.\TableList.txt';

select proc_name from sysprocedure
where creator = 3
 -- in case you use particular prefixes...
 -- and (proc_name like 'STP_%' or proc_name like 'FCTN_%')
order by proc_name;
output to '.\ProcList.txt';

select tname || '.' || trigname from systriggers
where owner = 'dbo'
order by 1;
output to '.\TriggerList.txt';

select 'tab ', stname, grantor, grantee
from systabauth
where screator = 'dbo' and stname not like 'sys%'
union
select 'proc', procname, creator, grantee
from sysprocauth
where creator = 'dbo'
order by 1, 2, 3, 4;
output to '.\GrantList.txt';

permanent link

answered 03 Jun '11, 08:16

Volker Barth
39.3k●355●539●809
accept rate: 33%

Thanks for the queries! Although I chose the simpler way, this is a great approach.

(15 Jun '11, 02:59) henginy

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

asa-9 ×107
schema ×25
comparison ×7

question asked: 31 May '11, 12:30

question was seen: 4,216 times

last updated: 15 Jun '11, 07:42

SAP SQL Anywhere

Schema comparison tool for ASA

Follow this question

Related questions