Help - looking for an automated schema documenter

[jontvr]

I've inherited an Oracle database with about 200 tables and no fixed referential structure. The naming convention specifies that each field contain a prefix identifying the table the field belongs to in, so the primary to foreign key structure is as follows:

select
a.a_pk, b.b_pk, c.c_pk
from
a, b, c
where
a.a_pk = b.b_a_pk
and
b.b_pk = c.c_b_pk

All automated schema documenters or reverse engineering applications that I've tried won't identify or vizualize the relationships because they expect the pk and fk names to be the same. Is there any tool out there where I can define the key structure?

Thanks

 

[SantaMufasa]

Jontvr,

Before we can give you an answer, you must confirm whether or not your Primary Keys and your Foreign Keys are declared explicitly as constraints in the database. If so, then yes, I can post a nice script that will graphically document the parent-child relationships amongst your tables.

It's 11:10 p.m. in my time zone, so I'm heading to bed soon. Please post your reply as soon as possible.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SantaMufasa]

BTW, Jontvr, when I say that I have a script "that will graphically document the parent-child relationships amongst your tables," I should have said that the script will visually document the hierarchy of parent-child relationships withing a schema.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[jontvr]

Thanks for your response. Unfortunately there are no explicit key constraints in this database. All referential integrity is maintained by triggers.

 

[SantaMufasa]

That is very unfortunate. What possible benefits can accrue to your application by using triggers versus declarative referential constraints? If you have declarative referential integrity, there are tons of packages that can offer you a solution (example: Oracle*Designer, Visio, Embarcadero DBArtisan, ERWin, my free script, et cetera).

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[jontvr]

I think you've pretty much nailed my problem on the head. Re-architecting this database isn't an option for me at this time. I know there are plenty of tools that can produce ER diagrams based on explicit relationships. It's the implied relationship that I'm stuck on. Every tool I've looked at will only imply a relationship if the field names are identical across tables. I need to be able to say that there is a link where a substr of the fk name equals the pk. Probably wishful thinking on my part, but I thought it worth asking. Thanks for your time.

 

[LKBrwnDBA]

If most of the FK's follow the "pattern" you describe, maybe you could code something like this to generate these constraints:

set term off lin 80 trims on pages 0 feed off
spo /tmp/fk.sql
Select 'Alter Table '||Table_Name||' Add Constraint '||Column_Name||Chr(10)
      ||'_Fk Foreign Key ('||Column_Name||') References '||Chr(10)
      ||Substr(Column_Name,1,Instr(Column_Name,'_')-1)||' ('
      ||Substr(Column_Name,Instr(Column_Name,'_')+1)||');'
  From User_Tab_Columns C
 Where Column_Name Like '%_Pk'
   And Exists (
       Select 1 From User_Tab_Column K
        Where Table_Name = Substr(Column_Name,1,Instr(Column_Name,'_')-1)
          And Column_Name = Substr(Column_Name,Instr(Column_Name,'_')+1));
spo off

And, review the /tmp/fk.sql file to see if you got at least some of the FK's.

Good luck!

NOTE: You may need a sequence to generate unique constraint names.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb