SchemaCrawler

SchemaCrawler is an open source, cross-platform and freely distributed Java API (Application Programming Interface) that has been designed from the ground up to help application developers work easily with database metadata. It is also a database schema documentation utility and database schema comprehension and discovery tool.

The software will help Java developers in finding columns and tables using regex (regular expressions), finding issues with schema design, to use the JavaScript scripting language for scripting against your database, as well as with database diagramming.

Supports any JDBC powered database

Another interesting feature of the SchemaCrawler software is that it supports any JDBC powered database. However, for your convenience, SchemaCrawler includes drivers for some of the most common relational database management systems.

I want to see a usage example!

If you want to see a usage example of how the SchemaCrawler software works, please take a look at the example below.

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 Database database = SchemaCrawlerUtility.getDatabase(connection, options);
for (final Schema schema: database.getSchemas())
{
System.out.println(schema);
for (final Table table: database.getTables(schema))
{
System.out.print("o--> " + table);
for (final Column column: table.getColumns())
{
System.out.println(" o--> " + column);
}
}
}

Under the hood

Taking a look under the hood of the SchemaCrawler project, we can notice that it has been written entirely in the Java programming language. We have successfully tested the software with computers supporting either of the 64 or 32-bit instruction set architectures.

Runs on Linux, BSD, Windows and Mac

Being written in Java, SchemaCrawler is a cross-platform software that works with any computer operating system where the Java Runtime Environment (JRE) is available, including GNU/Linux, BSD, Microsoft Windows, and Mac OS X.

What is new in this release:

• Fixing links on website.

What is new in version 12.04.02:

• Fix Oracle connection.

What is new in version 12.03.02:

• Better connection arguments.

What is new in version 10.08.02:

• Updated copyright notices.
• Fixed javadocs, using standard Java 7 stylesheet.

What is new in version 10.08.01:

• Trim all table names, to allow Informix to match correctly.

What is new in version 10.8:

• Providing richer information about table constraints, including details of constrained columns.

What is new in version 10.7:

• Providing richer information about table constraints. Renaming check constraints to table constraints.

What is new in version 10.6:

• Changed InclusionRule to an interface, to allow custom implementations.
• Fixed lack of support for type maps by the Sybase IQ database driver.

What is new in version 10.4:

• Fixed view definitions for PostgreSQL.
• Fixed retrieving index metadata for tables, working around PostgreSQL bugs #3480 #6253.
• Added retrieving index definitions for PostgreSQL and Oracle.
• Added retrieving view definitions for Apache Derby.

What is new in version 10.1:

• SchemaCrawler support for J2SE 7 only, since previous Java versions are deprecated. Converted the codebase over to use J2SE 7 constructs.
• New command-line option, -only-matching, to show only matching tables, and does not show foreign keys that reference other non-matching tables. Modeled after the analogous grep option.
• New option to set input encoding format for script files and templates. The option is "schemacrawler.encoding.input" in the SchemaCrawler properties file.
• Renamed command-line option -table_types to -tabletypes.

What is new in version 9.6:

• Fixed diagram generation on Linux when no GraphViz options are supplied.
• Added trigger names to JSON output by default. Added new configuration variable, schemacrawler.format.hide_trigger_names to suppress trigger names in all output formats.
• Added new -portablenames command-line option, to allow for easy comparison between databases, by hiding foreign key names, constraint names, trigger names, index and primary key names, and not showing the fully-qualified table name.

What is new in version 9.5:

• Updated HTML generation to have captions consistently for all tables. Numeric data is right-aligned in HTML, and correctly reported in JSON as well. These changes affect other output formats as well.
• Updated Oracle database connection URL to the new syntax.

What is new in version 9.4:

• Fix for honoring text formatting options in GraphViz dot output.

What is new in version 9.3.2:

• Fix for honoring sort options on JSON output.

What is new in version 9.3.1:

• Fix for generating diagrams on Unix.

What is new in version 9.3:

• Weak associations are returned sorted.
• Added ability to specify additional command line options for GraphViz, using either Java system properties, or environmental variables.

What is new in version 9.2:

• Fixed Windows script, sc.cmd.
• Better support for sqlite, including the latest database driver, reporting of foreign keys, and view definitions.
• Updated the jTDS JDBC driver to 1.2.6, for Microsoft SQL Server.
• Deprecated the getType() method on database objects.

What is new in version 9.1:

• Added examples for Groovy, Ruby and Python scripting.
• SchemaCrawler is now bundled as an OSGi jar.

What is new in version 9.0:

• The API has changed from 8.x versions. All API calls return collections instead of arrays. Schemas are not containers, but tags for database objects. Tables and routines (stored procedures and functions) are available directly on the database, and there are new finders to search by schema.
• Support for database functions has been added. All references to stored procedures in the code and documentation now refer to routines. Stored procedures and functions are treated in the same way, as routines.
• SchemaCrawler now allows queries to be specified on the command-line. If a command is not recognized, or is not a named query, it is executed as if it is a query.
• The output format includes trgger definitions as part of the schema, instead of as the detailed schema output.
• Database connections have to be specified on the command-line, by providing the driver class name and URL. Database connections can no longer be defined in properties files.
• There are new command-line options. -V, --version will print the SchemaCrawler version and exit. There are shorter and more standard (Unix-like) synonyms -u (for -user), -c (for -command), and -o (for -output). The -procedures switch has been renamed -routines.

What is new in version 8.17:

• The HTML output format outputs valid HTML5 and CSS3, as valid XML.
• JSON output supports all of the formatting options that the other text formats support.
• A new method, getShortName(), get the unqualified name for columns and other dependent objects.
• A new text format option, "schemacrawler.format.show_unqualified_names", shows the unqualified name in text output, for easier comparisons across different catalogs and schemas.

What is new in version 8.15:

• Added distribution for Sybase IQ.

What is new in version 8.11:

• Added ability to configure SchemaCrawler linters.
• Added a new lint for useless surrogate keys.
• Added ability to tag tables and columns with additional metadata attributes from SQL queries.

What is new in version 8.10:

• Changed copyright notice on source code.
• Added ability to use configuration from bundled versions of SchemaCrawler, so that triggers, and extended metadata are available.
• Started to add teh ability to configure SchemaCrawler linters.

What is new in version 8.9:

• add Added SchemaCrawler Lint as a separate report, with ability to add custom linters. add Added additional SchemaCrawler linters.
• add Added ability to extend SchemaCrawler with custom command.
• fix Fixed issue with constraints with the same name in different schemas.

What is new in version 8.8:

• Added JSON and TSV output formats. Sualeh Fatehi
• Moved SchemaCrawler Maven Plugin into a new SourceForge project.

What is new in version 8.7:

• New INFORMATION_SCHEMA views to provide Oracle metadata details. Sualeh Fatehi
• update Update database driver versions. Sualeh Fatehi
• fix Fixed bug #3392557 - NullPointer exception thrown when columns excluded in Oracle. Sualeh Fatehi
• fix Fixed bug #3392976 - Exception retrieving tables: Could not retrieve indices for Oracle schemas with $ in the name. Sualeh Fatehi
• fix Fixed bug #3401752 - Attempt to locate database 'dbo' by name failed.

What is new in version 8.6:

• New feature to chain, or run multiple SchemaCrawler commands using JavaScript.

What is new in version 8.5.1:

• Changed copyright to 2011. Sualeh Fatehi
• Fixed grep. Sualeh Fatehi
• Removed option to provide index info SQL. Sualeh Fatehi
• Better unit test coverage. Restructuring of Eclipse projects. Sualeh Fatehi

What is new in version 8.5:

• Changed copyright to 2011. Sualeh Fatehi
• Fixed grep. Sualeh Fatehi
• Removed option to provide index info SQL. Sualeh Fatehi
• Better unit test coverage. Restructuring of Eclipse projects.

What is new in version 8.4:

• SchemaCrawler commands have been simplified. For example, "list_objects" is now simply "list".
• -procedures= is used as a command line switch instead of -show_stored_procedures.
• Issues with obtaining PostgreSQL tables and Derby foreign keys are fixed.
• -infolevel is now a required command line argument.

What is new in version 8.3.2:

• Verbose output now contains column remarks along with table remarks.
• A bug with graphs that have foreign keys to tables not included in the grep has been fixed.

What is new in version 8.3.1:

• A new command line option, -urlx, passes additional JDBC URL properties to bundled distributions.
• Details on the "main" command line is in the how-to section of the official website.

What is new in version 7.6:

• Fixed specification of Oracle SID using the -database command line switch, as well as spcification of port number.
• Fixed shell script classpath.

What is new in version 7.3.1:

• Enhancement to the JavaSqlType class, to contain the SQL type group. New lookup by data-type name added to JavaSqlTypesUtility.
• Removed method, getTypeClass(), from ColumnDataType.
• Removed deprecated methods for grouping SQL data types (such as isReal()) from the ColumnDataType class.

What is new in version 7.2:

• Better modeling of privileges and grants.
• Bug fix for obtaining indexes and foreign keys with the Oracle driver.
• Added natural sort order for tables, based on the foreign keys, and corresponding new configuration option, and command-line switch, -sorttables. Also added new API methods to get child and parent tables, based on the foreign keys.
• Handle null schemas for databases that do not support schemas, such as MySQL.
• Allow data dumps when tables contain binary objects, by fixing the SELECT sort order.

What is new in version 7.2:

• Better modeling of privileges and grants.
• Bug fix for obtaining indexes and foreign keys with the Oracle driver.
• Added natural sort order for tables, based on the foreign keys, and corresponding new configuration option, and command-line switch, -sorttables. Also added new API methods to get child and parent tables, based on the foreign keys.
• Handle null schemas for databases that do not support schemas, such as MySQL.
• Allow data dumps when tables contain binary objects, by fixing the SELECT sort order.

What is new in version 7.1:

• This release makes efficient use of database connections and plays well with pooled connections.
• There are better log messages, including for configuration, for when a database connection is opened or closed.
• There are several performance enhancements, improving the speed of retrieval of the schema.

What is new in version 6.4:

• The 6.4 release adds a pre-packaged SchemaCrawler for SQLite. There is also an improved algorithm to find weak associations, and a new command-line switch to sort columns alphabetically.

What is new in version 6.3:

• SchemaCrawler grep functionality is built into the SchemaCrawler command-line. This means that all of the SchemaCrawler commands, including graphing, can use filtering and grep options. The command-line options help has been re-written. If no commands are specified, defaults to standard_schema (a new command), which provides the most commonly needed details of the schema. SchemaCrawler produces Graphviz DOT files to generate schema diagrams, as one of its native output formats.

What is new in version 6.2:

• A new feature to infer weak associations between tables, even if there is no foreign key. Ruby on Rails table schemes are supported, as well as other simple relationships. Table name prefixes are automatically detected.
• Database diagram support using Graphviz. Primary keys are indicated in a darker color than other columns. Foreign keys are indicated with arrow connectors. Weak associations between tables are shown in dashed lines. Multiple output formats are supported. Support for Jung is dropped.

What is new in version 6.0.2:

• This release fixes an issue with MySQL schemas.

What is new in version 6.0.1:

• Bugfixes for recognizing -schemapattern on the command line, and data types with the Oracle driver.

What is new in version 6.0:

• This release introduces a number of API changes and new features.
• Notable among these is full support for multiple schemata in a catalog.
• The SchemaCrawler API returns a catalog as the top level object, which contains schemata.
• Another change is that SchemaInfoLevel is an option, not a CrawlHandler property, to allow for better separation of concerns.
• Finally, SchemaCrawler has support for JavaScript scripting with J2SE 6 and above.

Requirements:

• Oracle Java Standard Edition Runtime Environment