How to Use the SchemaCrawler Command-line

  1. Explanation of the SchemaCrawler main Programs
  2. How to run an arbitrary query

Include and Exclude Stuff from the Output

  1. How to see why certain tables or columns for excluded
  2. How to include only significant columns - that is, columns that are part of a primary key or unique index, or columns that are foreign keys
  3. How to include or exclude certain tables or columns
  4. How to exclude database views from the output
  5. How to exclude routines, that is, stored procedures and functions from the output
  6. How to exclude database functions from the output

Create diff-able Output

  1. How to sort columns, foreign-keys and indexes alphabetically
  2. How to diff column data types across databases
  3. How to allow diffs of tables that have columns added in between
  4. How to hide display of object names that can change from server to server
  5. How to show table row counts in output and diagrams
  6. How to hide foreign key names, constraint names, trigger names, specific names for procedures, or index and primary key names
  7. How to hide catalog and schema names in text output
  8. How to hide catalog and schema colors in HTML output and graphs

Integrations

  1. How to script with your database
  2. How to create your own output format
  3. How to create a database diagram of your schema
  4. How to provide additional Graphviz command-line options

How to Use SchemaCrawler in Projects

  1. How to use SchemaCrawler programmatically
  2. How to use SchemaCrawler in a Maven Project
  3. How to use SchemaCrawler as an ant Task
  4. How to use SchemaCrawler To Produce a Maven Report

Advanced SchemaCrawler Usage

  1. How to get trigger, view, stored procedure and function definitions
  2. How to obtain check constraints
  3. How to get tables in "create" or "drop" order
  4. How to extend SchemaCrawler by adding a new command, new linter, or new database system support
  5. How to configure SchemaCrawler linters
  6. How to extend SchemaCrawler by adding a new linter
  7. How to fail a build with too many SchemaCrawler lints

How to Use the SchemaCrawler Command-line

Explanation of the SchemaCrawler main Programs

  • schemacrawler.Main

This is the most usual way to launch SchemaCrawler from the command-line. This launch offer a number of connection options, including by JDBC driver and URL, and by a connection defined in a properties configuration file. There are options to load configuration from properties files.

JDBC drivers and other external libraries must be available on the classpath for this application to function.

For help, use the -h command-line switch.

  • schemacrawler.tools.integration.spring.Main

An alternate to schemacrawler.Main, where configuration, including configuration of the SchemaCrawler command is done by means of a Spring Framework application context file.

JDBC drivers, Spring Framework libraries, and other external libraries must be available on the classpath for this application to function.

For help, use the -h command-line switch.

  • schemacrawler.utility.TestDatabase

Started the test database server, with a test schema, and test data. This is used for examples. Any schema or data modifications will be restored when the server is restarted.

JDBC drivers for HyperSQL, and other external libraries must be available on the classpath for this application to function.

[top]


How to run an arbitrary query

Run SchemaCrawler withquery, with a query for the command "-command=SELECT * FROM PUBLIC.BOOKS.AUTHORS" (The quotes are required.)

[top]

Include and Exclude Stuff from the Output

How to see why certain tables or columns for excluded

Re-run SchemaCrawler with -loglevel=ALL on the command-line.

[top]


How to include only significant columns - that is, columns that are part of a primary key or unique index, or columns that are foreign keys

Re-run SchemaCrawler with the -infolevel=standard -command=brief command-line options.

[top]


How to include or exclude certain tables or columns

Change the configuration for the SchemaCrawler the table or column include and exclude patterns in the schemacrawler.config.properties file. The include or exclude specification is a Java regular expression . The include pattern is evaluated first, and the exclusions are made from the included tables or columns list.

Also see the filtering and grep command-line options.

[top]


How to exclude database views from the output

Use the -tabletypes command-line option, without VIEW. For example, you can provide -tabletypes=TABLE. Further, see the details on the command-line options.

[top]


How to exclude routines, that is, stored procedures and functions from the output

The option in the configuration can be overridden by the -routines= command-line option. Further, see the details on the command-line options.

[top]


How to exclude database functions from the output

Use the -routinetypes=FUNCTION command-line option. Further, see the details on the command-line options.

[top]

Create diff-able Output

How to sort columns, foreign-keys and indexes alphabetically

Change the configuration for the SchemaCrawler "sort alphabetically" properties in the schemacrawler.config.properties file. Also see the sorting command-line options.

[top]


How to diff column data types across databases

Change the configuration for the SchemaCrawler schemacrawler.format.show_standard_column_type_names=true in the schemacrawler.config.properties file. This setting will show standard data types across different database systems. On the other hand, if you want to see the real database specific data types, change the setting to a value of true.

[top]


How to allow diffs of tables that have columns added in between

When columns are added into a table, they can change the column ordinal number. This can mess up the diffs. Change the configuration for the SchemaCrawler schemacrawler.format.show_ordinal_numbers=false in the schemacrawler.config.properties file. You can combine this setting with the setting to sort columns alphabetically to produce diff friendly output.

[top]


How to hide display of object names that can change from server to server

Use the -portablenames=true command-line option to allows for easy comparison between databases, by hiding foreign key names, constraint names, trigger names, specific names for procedures, and index and primary key names, and not showing the fully-qualified table name.

[top]


How to show table row counts in output and diagrams

Show table row counts in output, and diagrams. Change the configuration for the SchemaCrawler schemacrawler.format.show_row_counts=true in the schemacrawler.config.properties file.

[top]


How to hide foreign key names, constraint names, trigger names, specific names for procedures, or index and primary key names

If foreign key names, constraint names, trigger names, specific names for procedures, or index and primary key names are not explicitly provided while creating a schema, most database systems assign default names. These names can show up as spurious diffs in SchemaCrawler output. Change the configuration for the following properties in your schemacrawler.config.properties file.

All of these names can be hidden by using the -portablenames command-line option.

schemacrawler.format.hide_primarykey_names=false
schemacrawler.format.hide_foreignkey_names=false
schemacrawler.format.hide_index_names=false
schemacrawler.format.hide_trigger_names=false
schemacrawler.format.hide_routine_specific_names=false
schemacrawler.format.hide_constraint_names=false

[top]


How to hide catalog and schema names in text output

Change the configuration for the SchemaCrawler schemacrawler.format.show_unqualified_names=true in the schemacrawler.config.properties file. This setting will show unqualified names of database objects such as tables and prcodures. That is, the catalog and schema names will not be displayed. Use with care, especially if you have foreign keys that reference tables in other schemas, or synonyms.

[top]


How to hide catalog and schema colors in HTML output and graphs

Change the configuration for the SchemaCrawler schemacrawler.format.no_schema_colors=true in the schemacrawler.config.properties file. This setting will not show color-coded catalog and schema names in HTML and graph output.

[top]

Integrations

How to script with your database

SchemaCrawler has built-in support to be used with JavaScript scripts. Write your JavaScript file, assuming that a "catalog" variable containing the database schema will be available. A "connection" variable will also be available, and you will be able to execute SQL against your database. Run SchemaCrawler with the command-line options - -command script -outputformat <your script file> . See the example in the examples\javascript directory for more details.

[top]


How to create your own output format

SchemaCrawler integrates with Apache Velocity to allow for templated ouput. Put Velocity on your classpath, and create your template, and run SchemaCrawler with the command-line options - -command script -outputformat <your script file> . -command velocity -outputformat <your Velocity template> . See the Velocity example in the SchemaCrawler examples download.

[top]


How to create a database diagram of your schema

SchemaCrawler integrates with Graphviz to produce graph images. See the diagram example in the SchemaCrawler examples download. For more details, see the diagram section.

[top]


How to provide additional Graphviz command-line options

SchemaCrawler integrates with Graphviz to produce graph images. See the previous question for details.

You can provide additional Graphviz command-line options using the SC_GRAPHVIZ_OPTS environmental variable, or pass in the additional arguments using the SC_GRAPHVIZ_OPTS Java system property.

[top]

How to Use SchemaCrawler in Projects

How to use SchemaCrawler programmatically

Read Java API Makes Database Metadata as Easily Accessible as POJOs for an introduction to the SchemaCrawler API. (This article may refer to an older release of the SchemaCrawler API, but the concepts are the same.) You can also browse the javadocs .

See the api example in the SchemaCrawler examples download.

Or, if you are impatient, try code similar to the following:

final SchemaCrawlerOptions options = new SchemaCrawlerOptions();
// Set what details are required in the schema - this affects the
// time taken to crawl the schema
options.setSchemaInfoLevel(SchemaInfoLevel.standard());

final Catalog catalog = SchemaCrawlerUtility.getCatalog(connection, options);
for (final Schema schema: catalog.getSchemas())
{
  System.out.println(schema);
  for (final Table table: schema.getTables())
  {
    System.out.println("o--> " + table);
    for (final Column column: table.getColumns())
    {
      System.out.println("     o--> " + column);
    }
  }
}

[top]


How to use SchemaCrawler in a Maven Project

In order to use SchemaCrawler in your Maven projects, add a dependency to SchemaCrawler in your pom.xml.

<dependencies>
  ...
  <dependency>
    <groupId>us.fatehi</groupId>
    <artifactId>schemacrawler</artifactId>
    <version>14.14.03</version>
  </dependency>
</dependencies>
        

[top]


How to use SchemaCrawler as an ant Task

Call the SchemaCrawler command-line from ant, using the exec task.

[top]


How to use SchemaCrawler To Produce a Maven Report

SchemaCrawler Report Maven Plugin is a SourceForge project that provides a Maven plugin that can generate database documentation for a Maven-generated website.

[top]

Advanced SchemaCrawler Usage

How to get trigger, view, stored procedure and function definitions

See the documentation in INFORMATION_SCHEMA Views .

[top]


How to obtain check constraints

See the documentation in INFORMATION_SCHEMA Views .

[top]


How to get tables in "create" or "drop" order

Tables are sorted in alphabetical order by default. If you turn alphabetical sorting off, the tables will be displayed in "create" order - that is, tables with no foreign-key dependencies will be displayed first. The "drop" order is the reverse of the "create" order. Use the following command-line arguments to obtain tables in "create" order: -command=list -sorttables=false -routines=

[top]


How to extend SchemaCrawler by adding a new command, new linter, or new database system support

See the SchemaCrawler Plugins page.

[top]


How to configure SchemaCrawler linters

See SchemaCrawler Lint for details.

[top]


How to extend SchemaCrawler by adding a new linter

See SchemaCrawler Lint for details.

[top]


How to fail a build with too many SchemaCrawler lints

See SchemaCrawler Lint for details.

[top]

Back to top

Reflow Maven skin by Andrius Velykis.