Enhancing SchemaCrawler Output

SchemaCrawler can display view, stored procedure and function definitions, trigger information, and check constraints by using data from the INFORMATION_SCHEMA views. Even though INFORMATION_SCHEMA views are defined in the SQL Standard, not all database systems provide these views. However, if it is possible to construct a metadata view with columns that match the INFORMATION_SCHEMA views, then SchemaCrawler can use the data to obtain view, stored procedure and function definitions, trigger information, and check constraints. The INFORMATION_SCHEMA views currently processed by SchemaCrawler are shown below.

Any additional columns from the INFORMATION_SCHEMA views will be available using the getAttribute method on the SchemaCrawler Java object.

To get SchemaCrawler to use the views you will need to modify your database connection properties in your SchemaCrawler configuration file. Some examples are provided in the schemacrawler.config.properties file in the SchemaCrawler examples download.

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

Column name Description
CATALOG_NAME The name of the catalog.
SCHEMA_NAME The name of the schema.

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

INFORMATION_SCHEMA.VIEWS

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.
VIEW_DEFINITION The definition of the view as it would appear in a CREATE VIEW statement. If it does not fit, the value is NULL.
CHECK_OPTION One of: CASCADED = if WITH CHECK OPTION was specified in the CREATE VIEW statement that created the view; NONE = otherwise.
IS_UPDATABLE One of: YES = the view is updatable; NO = the view is not updatable.

INFORMATION_SCHEMA.ROUTINES

Column name Description
ROUTINE_CATALOG The name of the catalog containing the routine.
ROUTINE_SCHEMA The name of the schema containing the routine.
ROUTINE_NAME The name of the routine.
ROUTINE_BODY One of: SQL = the routine is an SQL routine; EXTERNAL = the routine is an external routine
ROUTINE_DEFINITION The text of the routine definition. If it does not fit, the value is NULL.

INFORMATION_SCHEMA.TRIGGERS

Column name Description
TRIGGER_CATALOG The name of the catalog containing the trigger.
TRIGGER_SCHEMA The name of the schema containing the trigger.
TRIGGER_NAME The name of the trigger.
EVENT_MANIPULATION The data manipulation event triggering execution of the trigger (the trigger event). One of: INSERT, DELETE, UPDATE
EVENT_OBJECT_CATALOG The name of the catalog containing the table or view on which the trigger is created.
EVENT_OBJECT_SCHEMA The name of the schema containing the table or view on which the trigger is created.
EVENT_OBJECT_TABLE The name of the table or view on which the trigger is created.
ACTION_ORDER Ordinal 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_CONDITION The 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_STATEMENT The character representation of the body of the trigger. If the length of the text exceeds 400 characters, the NULL value will be shown.
ACTION_ORIENTATION One of: ROW = the trigger is a row trigger; STATEMENT = the trigger is a statement trigger.
CONDITION_TIMING One 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.SEQUENCES

Column name Description
SEQUENCE_CATALOG The name of the catalog containing the sequence.
SEQUENCE_SCHEMA The name of the schema containing the sequence.
MINIMUM_VALUE Minimum value of the sequence.
MAXIMUM_VALUE Maximum value of the sequence.
INCREMENT The increment for the sequence.
CYCLE_OPTION One 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 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_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.
CONSTRAINT_TYPE One of: CHECK, FOREIGN KEY, PRIMARY KEY, UNIQUE
IS_DEFERRABLE One of: YES = the constraint is deferrable; NO = the constraint is not deferrable
INITIALLY_DEFERRED One of: YES = the constraint is deferred; NO = the constraint is immediate

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

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_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 or view.

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

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

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

Additional Metadata in SchemaCrawler Output

SchemaCrawler saves any additional metadata from the INFORMATION_SCHEMA queries. You can access the attributes of any SchemaCrawler object with getAttribute , and obtain these additional metadata attributes. You can also define your own queries to automatically define attributes.

If you create a query definition in the configuration properties, called select.ADDITIONAL_TABLE_ATTRIBUTES , they will be automatically added to the table metadata. 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.

If you create a query definition in the configuration properties, called select.ADDITIONAL_COLUMN_ATTRIBUTES , they will be automatically added to the column metadata. 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.

Back to top

Reflow Maven skin by Andrius Velykis.