[DB-SIG] Request: Standardized way to get database schema

brian zimmer bzimmer@ziclix.com
Wed, 14 Aug 2002 09:38:16 -0500


> 
> Wouldn't it be interesting and valuable (I'm pretty sure I'm 
> not the only one missing this feature) to work on a 
> DB-API extension (or a new version) for this ?
> 

For what it's worth, both mxODBC and zxJDBC provide access to this
information.  When I wrote zxJDBC I used the same API of mxODBC to
facilitate my migration.

> There is not that much information to get if you want to 
> remain standard w.r.t SQL:
> - list of tables,

.tables
 - list all tables {getTables}

> - list of columns for each table,
> - standard characteristics of columns (type, nullable, default value)

.columns
 - list the columns (with standard characteristics) {getColumns}

> - list of primary/foreign keys for each table (with target 
> table, and source and target columns)

.primarykeys {getPrimaryKeys}
.foreignkeys {getCrossReference}

> - indexes for each table

.statistics {getIndexInfo}

In addition, getting procedures is nice as well:

.procedures {getProcedures}
.procedurecolumns {getProcedureColumns}

The method name in {} is what zxJDBC calls on the JDBC DatabaseMetaData
instance to get the appropriate information.  You can get more
information about the parameters to each zxJDBC method from:

http://jython.org/docs/zxjdbc.html (look for Standard extensions...)

and for the JDBC methods from:

http://java.sun.com/j2se/1.3/docs/api/java/sql/DatabaseMetaData.html

hope this helps,

brian