Tool to generate data dictionary from SQL file

Question

Ok, so I got duped into doing some database design for an Oracle database where I work. The problem is, I'm not much of a DB guy :-). I'm currently using Rational Application Developer (RAD) to do the modeling of my database schema. What I'd ideally like to do is generate a series of Word documents, containing information from my DB schema (primarily table/column information, constraints, and triggers as tables in a Word doc).

I can't seem to find any way to do this with RAD, so I was wondering if anyone knows of a tool that can take an SQL DDL script file (containing the CREATE commands for the schema), and generate Word-type reports?

Any pointers greatly appreciated.

Thanks...

Answer 1

It's probably easiest just to run commands directly against the Oracle data dictionary tables themselves rather than trying to parse SQL files containing create statements.

For example, to get all the tables in schema X, you can do:

SELECT table_name FROM all_tables  WHERE owner = 'X'

To get all the columns for table 'T', owner 'U', you can do:

SELECT column_name FROM all_tab_columns WHERE table_name = 'T' AND owner = 'U'

Complete Example

Here's a complete example that would allow you to get all the tables and their columns back for all tables owned by 'owner'. It can be done with a single SQL statement:

SELECT t.table_name
     , c.column_name
  FROM all_tables t
     , all_tab_columns c
 WHERE t.TABLE_NAME = c.TABLE_NAME
   AND t.OWNER      = c.OWNER
   AND t.OWNER      = 'owner'
ORDER BY t.TABLE_NAME
       , c.COLUMN_NAME

As far as getting the data into Word documents, I'd just start with getting the data into text files first, then you can use other means to get it into Word if necessary. To get it into a text file, you can just run the above command in sql*plus, and just spool to a text file (i.e. issue a spool file.txt command in sql*plus) before running the SQL statement. Then your output will be written to a file.

Answer 2

Table reports among others, can be obtained from Oracle SQL Developer. However, one needs the access to the schema, to obtain the relevant information.

If you're modelling the database, and therefore, do not have access to a working schema, you could load the DDL file in a model maintained by Oracle Data Modeler (you might want to check the license of this first) and then obtain the relational model report (the report that reflects the physical database model in most parts).