Hi,

is there a way to compare the schemas of two databases?
I need this after processing an update to the schema to check if any changes
have been copied correctly.

In another database system, there was a function to create and export a
checksum for the schema (for each table). If I created a list with the
checksums for the original- and the customer-database and there was no
difference in the checksums... I knew, that everything is ok..
Is there sowas perhaps also in SA?

Regards

See
http://sqlanywhere.blogspot.com/2008...e-schemas.html

AFAIK there is no built-in schema checksum comparison.

Breck

On 26 Oct 2009 02:21:13 -0800, <Ingmar> wrote:

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter (AT) risingroad (DOT) com

thx Breck... seems to be a workable solution.

But do you have also any idea for ASA 9.
I ask because, although we begin shortly productively with SA11 but already
have many customers with ASA 9.

Ingmar

"Breck Carter [TeamSybase]" <NOSPAM__breck.carter (AT) gmail (DOT) com> schrieb im
Newsbeitrag news:7h1be55ginhk78kr8gr5mcle0e3tfrk73r (AT) 4ax (DOT) com...

Ingmar,

DUNLOAD -no was introduced with SA 11 whereas older ASA/SA versions just
have the -n option. Therefore with ASA9, the UNLOAD -n output of the
particular groups of DB objects will not be ordered by name but by
creation date. That *may* make the comparing a bit more difficult if the
creation order is different in different dbs.

Besides that, Breck's description should work with older versions, too -
you just have to adapt the path names.


OTOH, if you just want to make sure, you added the correct columns to a
few tables, you can also just write some SQL queries against the system
tables and compare that output, i.e. something like

select st.table_name, sc.column_name, sc.nulls, sc."check",
sc."default", sd.domain_name, sut.type_name
from (syscolumn sc key join systable st key join sysdomain sd) left join
sysusertype sut on sc.user_type = sut.type_id
where table_name in ('MyTable1', 'MyTable2', 'MyTable3')
order by table_name, column_name;
output to 'Update_Check\ColCheckList.txt';

When doing updates in our replication setup, I frequently use queries
like this to check if consolidated and remotes have the exact same
schema before and after a schema update.
It may be more more focussed on the objects where differences are expected.


HTH
Volker


Ingmar wrote:

thx Volker... good idea.

Ok... now have a further question...
Is there a built-in function or another way to generate a SQL script based
on the differences between DB-01 and DB-02? That is, I have had 2
databases,schemas, phy/log-db-modells etc. and looking for a way to generate
automatically a SQL script, which can update the DB-01 (may be with
PowerDesigner?).
With this script, then I want update all customer databases of type
DB-schema-01 to the schema of the DB02.

Ingmar

"Volker Barth" <No_VBarth (AT) Spam_GLOBAL-FINANZ (DOT) de> schrieb im Newsbeitrag
news:4ae80dae$1 (AT) forums-1-dub (DOT) ..

Ingmar,

with PowerDesigner you could reverse engineer an existing database and
save the model. Do the same thing with the second database und use one
of the models as archive model for the Database -> Modify Database
function on the other model (depending on which is your "master").

It generates a script to update the structure according to the
differences (or modifies the target database directly, if you really
want). It might be necessary to modify the script manually, we do this
mostly to change the order of the generated DDL commands. But we use
that method for all our releases.

HTH
Reimer


Ingmar wrote:

See the new improved blog post, and scroll down to...

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.

http://sqlanywhere.blogspot.com/2009...-improved.html

Breck

On 27 Oct 2009 09:58:52 -0800, <Ingmar> wrote:

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter (AT) risingroad (DOT) com