Using the databaseMetaData optional tool

The databaseMetaData optional tool creates functions and table functions corresponding to most of the methods in the java.sql.DatabaseMetaData interface.

Before you run the databaseMetaData optional tool, make sure that your classpath contains the Derby jar files, including derbytools.jar.

You can load and unload the databaseMetaData tool by using the SYSCS_UTIL.SYSCS_REGISTER_TOOL system procedure. See the Derby Reference Manual for information about this procedure.

To load the databaseMetaData tool, use the following statement:

call syscs_util.syscs_register_tool( 'databaseMetaData', true )

This command creates metadata functions and table functions in the current schema. The functions and table functions have the same names as the corresponding java.sql.DatabaseMetaData methods which they wrap. Once you have loaded this tool, you can filter and join these functions to create powerful metadata queries. For instance, the following query lists the column names and datatypes for all columns in tables created by users:

select t.table_schem, t.table_name, c.column_name, c.type_name
from table( getTables( null, '%', '%' ) ) t,
     table( getColumns( null, '%', '%', '%') ) c
where c.table_schem = t.table_schem
and c.table_name = t.table_name
and t.table_type = 'TABLE'
order by table_schem, table_name, column_name

A few DatabaseMetaData methods take array arguments. Because those arguments cannot be represented as Derby types, the arguments are eliminated. This means that the trailing types arguments to getTables() and getUDTs() have been eliminated. In addition, the following DatabaseMetaData methods do not have corresponding metadata routines:

When you have finished joining metadata results, you can drop this package of functions and table functions as follows:

call syscs_util.syscs_register_tool( 'databaseMetaData', false )
Related reference
Using the foreignViews optional tool
Using the luceneSupport optional tool
Using the simpleJson optional tool
Using the rawDBReader optional tool