There are a few tools I'm aware of, particularly in the oracle space (Quest schema manager, TOAD) that have this functionality. I'm not sure if any of them support SAP-DB.
You could contact SAP and ask them if they're aware of any SAP-DB compatible tools that do this (Daniel, are you aware of any?) I've found this ability very useful on large dev projects; last year I was DBA on a J2EE project with quite a complex data model (around 500 tables, 1100 indexes and 5800 constraints of one sort or another). During development the data model evolved rapidly. We Maintained a canonical model using a repository tool. We could do QA on update scripts with this technique and actually used it to generate them for quite a while. Without this automation, keeping the ten or so environments in sync would have been much more work. Even if you write the patch scripts by hand, being able to QA them in this manner is very useful. Keep the list posted if you write these scripts - I'd say that there could well be some subscribers who would find such a tool quite useful. -----Original Message----- From: Dave Guyer [mailto:[EMAIL PROTECTED] Sent: Wednesday, 12 March 2003 9:20 a.m. To: SAP List; Nigel Campbell (DSL AK) Subject: Can I diff two databases? Thank you, Nigel. Exactly what I was looking for. The short answer is that, no, SAP DB does not come with a database diff tool, but a script can be written to do the job. ************************************************** Nigel Campbell (DSL AK) [EMAIL PROTECTED] <mailto:Nigel.C%40datacom.co.nz> 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. _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
