Tool to create data dictionary from SQL file

Question

Ok, so I got a hoax in creating some kind of database project for the Oracle database where I work. The problem is that I'm not that many DB guy :-). I am currently using Rational Application Developer (RAD) to model my database schema. What I would ideally like to do is to generate a series of Word documents containing information from my database schema (primarily table / column information, constraints and triggers as tables in a Word document).

I can’t find a way to do this using RAD, so I was wondering if anyone knew of a tool that could take a SQL DDL script file (containing CREATE commands for the schema) and generate Word reports?

Any pointers really appreciated.

Thank...

Answer 1

Probably the easiest way is to simply 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 of "U", you can do:

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

Full example

Here is a complete example that will allow you to return all tables and their columns for all tables owned by the "owner". This 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

Word, , , Word, . , sql*plus (.. spool file.txt sql*plus) SQL. .

Answer 2

, , Oracle SQL Developer. .

, , , DDL , Oracle Data Modeler ( ), (, ).