Before you run the databaseMetaData optional tool, make sure
that your classpath contains the
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
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
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:
- getRowIdLifetime() is eliminated because
does not provide an
implementation of java.sql.RowIdLifetime.
- getSchemas() is eliminated because
does not support
overloads. The more general getSchemas( String, String ) method is
included.
- supportsConvert() is eliminated because
does not support
overloads. The more general supportsConvert( int, int ) is included.
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 )