INFORMATION_SCHEMA Views

Enhancing SchemaCrawler Output

SchemaCrawler can display view and procedure definitions, trigger information, and check constaints 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 and procedure definitions, trigger information, and check constaints. The INFORMATION_SCHEMA views currently processed by SchemaCrawler are shown below.

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 _distrib folder of the download.

For example, if you had defined a connection called hsqldb , going against the HSQLDB database, and wanted to get view definitions in the schema output, you would create a property in your schemacrawler.config.properties file like this: 

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

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

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

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 immediate; NO = the constraint is deferred

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.