Document Oracle database

Question

I've been asked to document an Oracle database. I don't need EER diagrams or anything fancy: a simple printable document with a description of tables and columns will do. However, I'd prefer not to type the column names for all the 79 tables. Is there any tool to extract this information from the database server, possibly with some basic information like data types and comments?

Table: Foo
Comment: Definition of system foo
Columns:
    Name       | Data type    | Default value | Allow NULL | Key         | Comment
    -----------------------------------------------------------------------------------------
    FOO_ID     | NUMBER(*,0)  | -             | NOT NULL   | Primary Key | Value from FOO_SEQ
    DATE_ADDED | DATE         | SYSDATE       | NOT NULL   | -           | -
    FOO_NAME   | VARCHAR2(50) | -             | NOT NULL   | Unique      | Foo identifier

I'm running Windows XP and the server is 11g.

Aftermath

I couldn't find a proper view index in the online Oracle documentation so I finally grabbed the PDF version and searched through it until I got these queries:

/*
 * Tables and views
 */
SELECT UO.OBJECT_NAME, UO.OBJECT_TYPE, UO.TEMPORARY, TC.COMMENTS
FROM USER_OBJECTS UO
LEFT JOIN USER_TAB_COMMENTS TC ON UO.OBJECT_NAME=TC.TABLE_NAME
WHERE UO.OBJECT_TYPE IN ('TABLE', 'VIEW')
ORDER BY UO.CREATED, UO.OBJECT_ID

/*
 * Columns
 */
SELECT TC.TABLE_NAME, TC.COLUMN_NAME,
    TC.DATA_TYPE, TC.DATA_LENGTH, TC.DATA_PRECISION, TC.DATA_SCALE, TC.CHAR_USED,
    TC.DATA_DEFAULT, TC.NULLABLE, CC.COMMENTS
FROM USER_TAB_COLUMNS TC
LEFT JOIN USER_COL_COMMENTS CC ON TC.TABLE_NAME=CC.TABLE_NAME AND TC.COLUMN_NAME=CC.COLUMN_NAME
ORDER BY TC.TABLE_NAME, TC.COLUMN_ID

/*
 * Restrictions
 */
SELECT UC.TABLE_NAME, UC.CONSTRAINT_NAME, UC.CONSTRAINT_TYPE, /*UC.SEARCH_CONDITION, UC.R_CONSTRAINT_NAME, UC.DELETE_RULE,*/
    CC.COLUMN_NAME, CC.POSITION
FROM USER_CONSTRAINTS UC
LEFT JOIN USER_CONS_COLUMNS CC ON UC.CONSTRAINT_NAME=CC.CONSTRAINT_NAME
ORDER BY UC.TABLE_NAME, UC.CONSTRAINT_TYPE, UC.CONSTRAINT_NAME, CC.POSITION

Getting tables in chronological order (the order in which the installer created them) allows me to document them in a logical order (I wrote the installer after all).

Answer 1

The metadata can be extracted from ALL_TABLES and ALL_TAB_COLUMNS.

TOAD offers a facility to extract reports like the above, but I'd probably do it in straight SQL if it's a one-off.