Schemacrawler logo

SchemaCrawler

Free database schema discovery and comprehension tool

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.CHECK_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.

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

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.TABLE_PRIVILEGES

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

DATABASE_METADATA.TABLE_COLUMN_PRIVILEGES

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

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.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_HIDDEN_TABLE_COLUMNS

Column name Description
TABLE_CATALOG The name of the catalog containing the table.
TABLE_SCHEMA The name of the schema containing the table.
TABLE_NAME The name of the table.
COLUMN_NAME The name of the hidden column.

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.
REMARKS Comments or remarks about the index.
INDEX_DEFINITION The definition of the index.

METADATA_EXTENSION.EXT_TABLE_CONSTRAINTS

Column name Description
CONSTRAINT_CATALOG The name of the catalog containing the table constraint.
CONSTRAINT_SCHEMA The name of the schema containing the table constraint.
CONSTRAINT_NAME The name of the table constraint.
TABLE_NAME The name of the table which has the table constraint.
REMARKS Comments or remarks about the table constraint.
CONSTRAINT_DEFINITION The definition of the table constraint.

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.SERVER_INFORMATION

If you create a query definition in the configuration properties, called select.ADDITIONAL_INFO.SERVER_INFORMATION, the database server specific information will be added to the catalog metadata, and SchemaCrawler output. The query should return the following columns:

Column name Description
NAME The name of the server information property.
VALUE The value of the server information property.
DESCRIPTION The description of the server information property.

ADDITIONAL_INFO.DATABASE_USERS

If you create a query definition in the configuration properties, called select.ADDITIONAL_INFO.DATABASE_USERS, database users and their attributes will be added to the catalog metadata. The query should return the following columns:

Column name Description
USERNAME The datbase user’s username.
Any additional columns for database user 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.