Data Dictionary Extensions

Enhancing SchemaCrawler Output

SchemaCrawler can display view, stored procedure and function definitions, trigger information, and check constraints by using data from a database’s data dictionary views. Views currently processed by SchemaCrawler are shown below. Any additional columns from these views will be available using the getAttribute method on the SchemaCrawler Java objects.

To get SchemaCrawler to use the views, you will need to modify your SchemaCrawler configuration file located in config/schemacrawler.config.properties. For example, if you want to get view definitions in the schema output, you would create a property in your schemacrawler.config.properties file like this:

select.INFORMATION_SCHEMA.VIEWS=\
  SELECT \
    * \
  FROM \
    INFORMATION_SCHEMA.SYSTEM_VIEWS

INFORMATION_SCHEMA Views

INFORMATION_SCHEMA.SCHEMATA

Column nameDescription
CATALOG_NAMEThe name of the catalog.
SCHEMA_NAMEThe name of the schema.

INFORMATION_SCHEMA.SEQUENCES

Column nameDescription
SEQUENCE_CATALOGThe name of the catalog containing the sequence.
SEQUENCE_SCHEMAThe name of the schema containing the sequence.
MINIMUM_VALUEMinimum value of the sequence.
MAXIMUM_VALUEMaximum value of the sequence.
INCREMENTThe increment for the sequence.
CYCLE_OPTIONOne of: YES = the sequence continues to generate values after reaching its maximum value; NO = the sequence does not generate values after reaching its maximum value.

INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Column nameDescription
CONSTRAINT_CATALOGThe name of the catalog containing the table constraint.
CONSTRAINT_SCHEMAThe name of the schema containing the table constraint.
CONSTRAINT_NAMEThe name of the table constraint.
TABLE_CATALOGThe name of the catalog containing the table or view.
TABLE_SCHEMAThe name of the schema containing the table or view.
TABLE_NAMEThe name of the table or view.
CONSTRAINT_TYPEOne of: CHECK, FOREIGN KEY, PRIMARY KEY, UNIQUE
IS_DEFERRABLEOne of: YES = the constraint is deferrable; NO = the constraint is not deferrable
INITIALLY_DEFERREDOne of: YES = the constraint is deferred; NO = the constraint is immediate

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

Column nameDescription
CONSTRAINT_CATALOGThe name of the catalog containing the table constraint.
CONSTRAINT_SCHEMAThe name of the schema containing the table constraint.
CONSTRAINT_NAMEThe name of the table constraint.
TABLE_CATALOGThe name of the catalog containing the table or view.
TABLE_SCHEMAThe name of the schema containing the table or view.
TABLE_NAMEThe name of the table or view.
COLUMN_NAMEThe name of the table or view.

INFORMATION_SCHEMA.VIEWS

Column nameDescription
TABLE_CATALOGThe name of the catalog containing the view.
TABLE_SCHEMAThe name of the schema containing the view.
TABLE_NAMEThe name of the view.
VIEW_DEFINITIONThe definition of the view as it would appear in a CREATE VIEW statement. If it does not fit, the value is NULL.
CHECK_OPTIONOne of: CASCADED = if WITH CHECK OPTION was specified in the CREATE VIEW statement that created the view; NONE = otherwise.
IS_UPDATABLEOne of: YES = the view is updatable; NO = the view is not updatable.

INFORMATION_SCHEMA.TRIGGERS

Column nameDescription
TRIGGER_CATALOGThe name of the catalog containing the trigger.
TRIGGER_SCHEMAThe name of the schema containing the trigger.
TRIGGER_NAMEThe name of the trigger.
EVENT_MANIPULATIONThe data manipulation event triggering execution of the trigger (the trigger event). One of: INSERT, DELETE, UPDATE
EVENT_OBJECT_CATALOGThe name of the catalog containing the table or view on which the trigger is created.
EVENT_OBJECT_SCHEMAThe name of the schema containing the table or view on which the trigger is created.
EVENT_OBJECT_TABLEThe name of the table or view on which the trigger is created.
ACTION_ORDEROrdinal number for trigger execution. This number will define the execution order of triggers on the same table and with the same value for EVENT_MANIPULATION, ACTION_CONDITION, CONDITION_TIMING and ACTION_ORIENTATION. The trigger with 1 in this column will be executed first, followed by the trigger with 2, etc.
ACTION_CONDITIONThe character representation of the search condition in the WHEN clause of the trigger. If the length of the text exceeds 400 characters, the NULL value will be shown.
ACTION_STATEMENTThe character representation of the body of the trigger. If the length of the text exceeds 400 characters, the NULL value will be shown.
ACTION_ORIENTATIONOne of: ROW = the trigger is a row trigger; STATEMENT = the trigger is a statement trigger.
CONDITION_TIMINGOne of: BEFORE = the trigger is executed before the triggering data manipulation operation; INSTEAD OF = the trigger is executed instead of the triggering data manipulation operation; AFTER = the trigger is executed after the triggering data manipulation operation.

INFORMATION_SCHEMA.ROUTINES

Column nameDescription
ROUTINE_CATALOGThe name of the catalog containing the routine.
ROUTINE_SCHEMAThe name of the schema containing the routine.
ROUTINE_NAMEThe name of the routine.
ROUTINE_BODYOne of: SQL = the routine is an SQL routine; EXTERNAL = the routine is an external routine
ROUTINE_DEFINITIONThe text of the routine definition. If it does not fit, the value is NULL.

Database Metadata Views

Database Metadata Views

SchemaCrawler obtains database metadata from the JDBC driver. However, it is possible to override the metadata obtained from the JDBC driver using custom views. SchemaCrawler can override the following sources of database metadata.

DATABASE_METADATA.TABLES

For details on the columns in this view, please refer to getTables

DATABASE_METADATA.TABLE_COLUMNS

For details on the columns in this view, please refer to getColumns

DATABASE_METADATA.FOREIGN_KEYS

For details on the columns in this view, please refer to getImportedKeys

DATABASE_METADATA.INDEXES

For details on the columns in this view, please refer to getIndexInfo

DATABASE_METADATA.OVERRIDE_TYPE_INFO

For details on the columns in this view, please refer to getTypeInfo

DATABASE_METADATA.PRIMARY_KEYS

For details on the columns in this view, please refer to getPrimaryKeys

Metadata Extension Views

METADATA_EXTENSION.EXT_TABLES

Column name Description
TABLE_CATALOG The name of the catalog containing the view.
TABLE_SCHEMA The name of the schema containing the view.
TABLE_NAME The name of the view.
TABLE_DEFINITION The definition of the table as it would appear in a CREATE TABLE statement. If it does not fit, the value is NULL.

METADATA_EXTENSION.EXT_TABLE_CONSTRAINTS

Column name Description
CONSTRAINT_CATALOG The name of the catalog containing the constraint.
CONSTRAINT_SCHEMA The name of the schema containing the constraint.
CONSTRAINT_NAME The name of the constraint.
CHECK_CLAUSE The search condition used in the check clause. If it does not fit, the value is NULL.

METADATA_EXTENSION.EXT_PRIMARY_KEYS

Column name Description
PRIMARY_KEY_CATALOG The name of the catalog containing the primary key.
PRIMARY_KEY_SCHEMA The name of the schema containing the primary key.
PRIMARY_KEY_TABLE_NAME The name of the table containing the primary key.
PRIMARY_KEY_NAME The name of the primary key.
PRIMARY_KEY_DEFINITION The definition of the primary key.

METADATA_EXTENSION.EXT_FOREIGN_KEYS

Column name Description
FOREIGN_KEY_CATALOG The name of the catalog containing the foreign key.
FOREIGN_KEY_SCHEMA The name of the schema containing the foreign key.
FOREIGN_KEY_TABLE_NAME The name of the table containing the foreign key.
FOREIGN_KEY_NAME The name of the foreign key.
FOREIGN_KEY_DEFINITION The definition of the foreign key.

METADATA_EXTENSION.EXT_HIDDEN_TABLE_COLUMNS

For details on the columns in this view, please refer to getColumns

METADATA_EXTENSION.EXT_SYNONYMS

Column name Description
SYNONYM_CATALOG The name of the catalog containing the synonym.
SYNONYM_SCHEMA The name of the schema containing the synonym.
SYNONYM_NAME The name of the synonym.
REFERENCED_OBJECT_CATALOG The name of the catalog containing the referenced object.
REFERENCED_OBJECT_SCHEMA The name of the schema containing the referenced object.
REFERENCED_OBJECT_NAME The name of the referenced object.

METADATA_EXTENSION.EXT_INDEXES

Column name Description
INDEX_CATALOG The name of the catalog containing the index.
INDEX_SCHEMA The name of the schema containing the index.
INDEX_NAME The name of the index.
TABLE_NAME The name of the table which has the index.
INDEX_DEFINITION The definition of the index.

METADATA_EXTENSION.EXT_INDEX_COLUMNS

Column name Description
INDEX_CATALOG The name of the catalog containing the index.
INDEX_SCHEMA The name of the schema containing the index.
INDEX_NAME The name of the index.
TABLE_NAME The name of the table which has the index.
COLUMN_NAME The name of the table column which has the index.
INDEX_COLUMN_DEFINITION The definition of the index column .

Additional Metadata

Additional Metadata in SchemaCrawler Output

SchemaCrawler saves any additional metadata from the view queries as attibutes on the SchemaCrawler Java objects. You can access the attributes with getAttribute. You can also define your own queries to define additional attributes.

ADDITIONAL_INFO.ADDITIONAL_TABLE_ATTRIBUTES

If you create a query definition in the configuration properties, called select.ADDITIONAL_INFO.ADDITIONAL_TABLE_ATTRIBUTES, the columns will be automatically added to table metadata as attributes. The query should return the following columns:

Column name Description
TABLE_CATALOG The name of the catalog containing the table or view.
TABLE_SCHEMA The name of the schema containing the table or view.
TABLE_NAME The name of the table or view.
… additional columns Any additional values that should be added to the table metadata.

ADDITIONAL_INFO.ADDITIONAL_COLUMN_ATTRIBUTES

If you create a query definition in the configuration properties, called select.ADDITIONAL_INFO.ADDITIONAL_COLUMN_ATTRIBUTES, the columns will be automatically added to the column metadata as attributes. The query should return the following columns:

Column name Description
TABLE_CATALOG The name of the catalog containing the table or view.
TABLE_SCHEMA The name of the schema containing the table or view.
TABLE_NAME The name of the table or view.
COLUMN_NAME The name of the table column.
… additional columns Any additional values that should be added to the column metadata.