600 tables in DDBB

I'm a very young software architect. Now I'm working in a very large and I have to lead a group of developers to rewrite all the mortgage system of the bank.

I'm looking at database tables and I realize that there is no any data model, neither documentation. The worst part is that there are about 1000 tabl开发者_运维问答es in dev environment, and like 600 in production. I trust more the production environment, but anyway, what can I do? I mean, I can suicide me or something, but is there any good reverse engineering tool, so at least I could get the schema definition with the relations between tables and comments extracted from the fields? Can you advice me something?

Thanks in advance.

If you are lucky and the database actually uses primary and foreign keys, you can get some excellent documentation with SchemaSpy, a nice command line tool written in Java.

Update: I've just remembered that Oracle SQL Developer has a similar tool (create a connection, right click on its icon and choose "Generate DB Doc") though it doesn't draw graphs.

Try connecting with a tool called TOAD - it has been some years since i used it but IIRC you could select the appropriate schema that you want to inspect and it will give you a tree view with all the tables and views and you can expand the table nodes to see the column details. No doubt the tool has moved on considerably since i last used it.

You can extract the comments like so

select * from dba_tab_comments 
 where owner not in ('SYS', 'SYSTEM')

and

select * from dba_col_comments 
 where owner not in ('SYS', 'SYSTEM')

As for reverse engeneering: if you're going to draw an ERD with 600+ tables, this is (probably) going to be too large anyway. I'd first try to find "clusters" of related tables and then use a specialized tool to draw these clusters.

Obviously, you want to make sure that the entire schema has foreign keys enforced. You might want to look at

select * from dba_constraints 
 where constraint_type = 'R' and 
       owner not in ('SYS', 'SYSTEM')

to see if all foreign keys.

Unfortunately this is oracle. For mysql there is a pretty nice tool called Mysql Workbench.