In most cases you're interested in relatively few types of
objects (tables, indexes, constraints). Stored procedures
and such items can just be loaded directly into the database
you're updating. The proprietary tools that do this often
tackle a larger problem than is necessary.
I've written scripts that do this on Oracle from the data
dictionary. The data dictionary in SAP-DB has enough
information to do this as well. Typically you write queries
that generate snippets of SQL directly, A query of this sort
would look something like this:
select 'alter table '||tablename||
'add column '||columnname||
case when c1.datatype in ('VARCHAR', 'CHAR')
then c1.datatype ||' ('||to_char(c1.len)||')'
when c1.datatype = 'FIXED'
then 'FIXED ('||c1.to_char(len)||','||
c1.to_char(dec)||')'
when [ various other data types ]
then [ ... ]
end
from domain.columns c1,
where not exists (select 1
from domain.columns c2
where c1.tablename = c2.tablename
and c1.columnname = c2.columnname
and c1.owner = :1
and c2.owner = :2)
(Note that I just knocked this together as an example - it's
not complete let alone tested) You could also do the post-
processing in a scripting language like Python.
Off the top of my head, these would be the main queries (YMMV):
New tables
Deleted tables
New columns
Deleted columns
Columns that have changed data type or nullability
New and deleted indexes
Columns added to indexes
Columns deleted from indexes
New and deleted primary keys, unique indexes and foreign keys
Columns added to pk's, uk's and fk's
Columns deleted from same
Constraints with changed definitions
You can just copy the code of stored procedures and suchlike
across. A DIFF operation is not needed unless you need to
know what's changed (to do this, extract the SQL and do the
diff outside the database)
If you can't find a schema diff utility that works with SAP-DB,
this will work. Hope it helps.
Nigel.
-----Original Message-----
From: daveg2 [mailto:[EMAIL PROTECTED]
Sent: Saturday, 8 March 2003 3:40 a.m.
To: [EMAIL PROTECTED]
Subject: Re: Can I diff two databases?
Thank you for the response, Nigel.
>Can you be a little more specific? Do you want to look for changes in the
>schema or changes in data between two versions? If you want to look for
>schema changes, you can query the data dictionary if the schemas are on the
>same instance.
I want to look for differences in the schema between two different
instances.
After determining the differences, I want to be able to apply the difference
to
one of the instances. The goal on task completion is to have identical
schema
in the two instances. Data is not part of this task.
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
