Schemacrawler logo

SchemaCrawler

Free database schema discovery and comprehension tool

SchemaCrawler - How-to

SchemaCrawler Command-line

  1. How to run the SchemaCrawler command-line

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
  7. How to exclude database table and column remarks or comments from the output
  8. How to do case-insenstive filtering

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 schema diagrams

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
  5. How to use pure-Java Graphviz

How to Use SchemaCrawler in Projects

  1. How to use SchemaCrawler programmatically
  2. How to use SchemaCrawler in an Apache Maven Project
  3. How to use SchemaCrawler as an ant Task
  4. How to use SchemaCrawler To Produce an Apache 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
  8. How to obtain PostgreSQL materialized views

How to Use the SchemaCrawler Command-line

Explore the SchemaCrawler command-line with a live online tutorial. The tutorial works from within any browser with no software or plugins needed.

Use schemacrawler.Main to launch SchemaCrawler from the command-line. This launch offers 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.

Include and Exclude Stuff from the Output

How to see why certain tables or columns for excluded

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

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 --info-level=standard --command=brief command-line options.

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.

How to exclude database views from the output

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

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.

How to exclude database functions from the output

Use the --routine-types=PROCEDURE command-line option. Further, see the details on the command-line options.

How to exclude database table and column remarks or comments from the output

Use the --no-remarks command-line option. Further, see the details on the command-line options.

How to do case-insenstive filtering

For any SchemaCrawler argument that uses regular expressions, you can use mode modifiers for case-insensitive matches. For example, to grep for column names, use a regular expression like --grep-columns='(?i).*data'

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.

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.

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.

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

Use the --portable=names or --portable=broad command-line option to allow 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.

How to hide table row counts in output and diagrams

Hide table row counts in output, and diagrams. Change the configuration for the SchemaCrawler schemacrawler.format.hide_table_row_counts=true in the schemacrawler.config.properties file. Then even if you run SchemaCrawler with the --load-row-counts command-line option, table row counts will not be shown in the output.

How to hide empty tables in output and diagrams

Hide tables with no data in output, and diagrams. Run SchemaCrawler with the --load-row-counts --no-empty-tables command-line options, while table row counts will be shown in the output, empty tables will be omitted.

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 --portable=names command-line option.

schemacrawler.format.hide_primarykey_names=false
schemacrawler.format.hide_foreignkey_names=false
#schemacrawler.format.hide_alternatekeys_names=false
schemacrawler.format.hide_weakassociation_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

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

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

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 schema diagram output.

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 --script <your script file> . See the example in the examples\javascript directory for more details.

How to create your own output format

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

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.

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.

How to use pure-Java Graphviz

In certain situations, it may not be possible to install Graphviz. SchemaCrawler integrates with nidi3/graphviz-java to provide a pre-Java approach to generating graphs. Download the SchemaCrawler distribution, and run through the diagram example. This will download the required jar files automatically.

How to Use SchemaCrawler in Projects

How to use SchemaCrawler programmatically

Read How to Get Database Metadata as Java POJOs, and browse the javadocs.

See the api example in the SchemaCrawler examples download.

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

More code examples are at [Code Examples Using the SchemaCrawler API](code-examples.html).

How to use SchemaCrawler in an Apache Maven Project

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

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

How to use SchemaCrawler as an ant Task

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

How to use SchemaCrawler To Produce an Apache Maven Report

The SchemaCrawler Report Maven Plugin can generate database documentation for an Apache Maven-generated website.

Advanced SchemaCrawler Usage

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

See the documentation in Extensions Using the Data Dictionary .

How to obtain check constraints

See the documentation in Extensions Using the Data Dictionary .

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 --sort-tables=false --routines=

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

See the SchemaCrawler Plugins page.

How to configure SchemaCrawler linters

See SchemaCrawler Lint for details.

How to extend SchemaCrawler by adding a new linter

See SchemaCrawler Lint for details.

How to fail a build with too many SchemaCrawler lints

See SchemaCrawler Lint for details.

How to obtain Oracle and PostgreSQL materialized views

PostgreSQL materialized views are a table type called “MATERIALIZED VIEW”. Use an additional SchemaCrawler command-line option, "--table-types=TABLE,VIEW,MATERIALIZED VIEW". Notice the double-quotes, since there is a space between MATERIALIZED and VIEW.