on 2011 May 31 1:30 PM
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.
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.
Request clarification before answering.
Probably the simplest thing to do is to do a dbunload -n
on both databases and then use diff
to compare them.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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:(
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
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';
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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....
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
52 | |
8 | |
5 | |
5 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.