# 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:

- getRowIdLifetime() is eliminated because Derby does not provide an implementation of java.sql.RowIdLifetime.
- getSchemas() is eliminated because Derby does not support overloads. The more general getSchemas( String, String ) method is included.
- supportsConvert() is eliminated because Derby 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 )

Parent topic: Optional tools
Related reference
Using the foreignViews optional tool
Using the luceneSupport optional tool
Using the simpleJson optional tool
Using the rawDBReader optional tool