General
Supported Platforms
SchemaCrawler Database Diagrams
SchemaCrawler Grep
SchemaCrawler Features
Distribution
SchemaCrawler is an open-source Java API that makes working with database metadata as easy as working with plain old Java objects.
SchemaCrawler is also a command-line tool to output your database schema and data in a readable form. The output is designed to be diff-ed with previous versions of your database schema.
| [top] |
SchemaCrawler can produce plain text output, XHTML, or comma-separated text (CSV). The XHTML output is a combination of valid XML (that can be manipulated by XML tools or XSLT), and HTML that can be viewed in a browser. SchemaCrawler can produce output in the DOT format, which GraphViz can convert into schema diagrams.
| [top] |
SchemaCrawler can produce brief, basic, or verbose details of your schema. The brief schema option gives you just table, view, and procedure names. The basic schema option gives you the column names and primary keys. The verbose schema option will give you details of data types, indices, primary and foreign keys, view and procedure definitions. The maximum schema option will give you details of privileges, triggers, and check constraints. Each successive option takes more time to execute.
SchemaCrawler can also manipulate your data. You can get counts of the rows in tables and views. SchemaCrawler can output all the data in your tables and views, or run specific SQL statements against table and views. SchemaCrawler can be fine-tuned using configuration files.
--- Connection Options ---
Required, one of:
-c <connection_name> (short for -connection <connection_name>)
Uses a named connection
-d (short for -default)
Uses the default connection
Or, the following:
-driver=<driver-class-name>
Fully qualified name of the JDBC driver class.
-url=<url>
JDBC connection URL to the database.
-user=<user>
Database user name.
-password=<password>
Database password.
--- Commands ---
-command=<command>
Where <command> is a comma-separated list of:
standard_schema
Shows the commonly needed detail of the schema, including details of
tables, views and procedures, columns, primary keys, indexes,
and foreign keys
brief_schema
Shows only table, view and procedure names
basic_schema
Shows more details of tables, views and procedures, including columns
and primary keys
verbose_schema
Shows the most detail of the schema, including data types, indexes,
foreign keys, and view and procedure definitions
maximum_schema
Shows maximum possible detail of the schema, including privileges, and
details of privileges, triggers, and check constraints
count
Shows counts of rows in the tables
dump
Shows data from all rows in the tables
<query_name>
Shows results of query <query_name>, as specified in the configuration
properties file
The query itself can contain the variables ${table}, ${columns}
and ${tabletype}, or system properties referenced as
${<system-property-name>}
Queries without any variables are executed exactly once; wueries
with variables are executed once for each table, with the variables
substituted
Optional, default is standard_schema
--- Sort Options ---
-sorttables=<boolean>
Sort tables alphabetically
<boolean> can be true or false
Optional, defaults to true
-sortcolumns=<boolean>
Sort columns in a table alphabetically, defaults to false
<boolean> can be true or false
Optional, defaults to false
-sortinout=<boolean>
Sort parameters in a stored procedure alphabetically
<boolean> can be true or false
Optional, defaults to false
--- Filtering Options ---
-catalogs=<regular-expression>
<regular-expression> is a regular expression to match catalog names
for example,
-catalogs=.*\.C.*|.*\.P.*
matches any catalogs whose names start with C or P
Catalogs that do not match the pattern are not displayed
Optional, defaults to showing all catalogs
-schemas=<regular-expression>
<regular-expression> is a regular expression to match schema names
for example,
-schemas=.*\.C.*|.*\.P.*
matches any schemas whose names start with C or P
Schemas that do not match the pattern are not displayed
Optional, defaults to showing all schemas
-schemapattern=<schemapattern>
<schemapattern> is a JDBC pattern (which can include _ or %)
for the schemas to crawl and optimizes the retrieval of
tables; if the -schemas option is used, then it is best
to specify this option in addition
Optional, defaults to all schemas in the catalog
-table_types=<table_types>
<table_types> is a command separated list of table types of
TABLE,VIEW,SYSTEM_TABLE,GLOBAL_TEMPORARY,LOCAL_TEMPORARY,ALIAS
Optional, defaults to TABLE,VIEW
-show_stored_procedures
Whether to show procedures, or not
Optional, defaults to true
-tables=<regular-expression>
<regular-expression> is a regular expression to match table
names - for example,
-tables=C.*|P.*
matches any table whose names start with C or P
Tables that do not match the pattern are not displayed
Optional, defaults to showing all tables
-excludecolumns=<regular-expression>
<regular-expression> is a regular expression to match fully qualified
column names, in the form "SCHEMANAME.TABLENAME.COLUMNNAME" - for example,
-excludecolumns=.*\.STREET|.*\.PRICE
matches columns named STREET or PRICE in any table
Columns that match the pattern are not displayed
Optional, default is to show all columns
-procedures=<regular-expression>
<regular-expression> is a regular expression to match procedure
names - for example,
-procedures=C.*|P.*
matches any procedure whose names start with C or P
Procedures that do not match the pattern are not displayed
Optional, defaults to showing all procedures
-excludeinout=<regular-expression>
<regular-expression> is a regular expression to match fully qualified
parameter names - for example,
-excludeinout=@p1|@p2
matches parameters named @p1 or @p2 in any procedure
Parameters that match the pattern are not displayed
Optional, default is to show all parameters
--- Grep Options ---
-grepcolumns=<regular-expression>
<regular-expression> is a regular expression to match fully qualified
column names, in the form "SCHEMANAME.TABLENAME.COLUMNNAME" - for example,
-grepcolumns=.*\.STREET|.*\.PRICE
matches columns named STREET or PRICE in any table
Optional, default is no grep
-grepinout=<regular-expression>
<regular-expression> is a regular expression to match fully qualified
inout names, in the form "SCHEMANAME.TABLENAME.INOUTNAME" - for example,
-grepinout=.*\.STREET|.*\.PRICE
matches inouts named STREET or PRICE in any table
Optional, default is no grep
-v (short for -invert-match)
Optional, inverts the match, and shows non-matching tables and columns
--- Configuration Options ---
-g=<config-file> (short for -configfile <config-file>)
Reads SchemaCrawler configuration properties from <config-file>
Optional, uses the default schemacrawler.config.properties file
in the current directory, or in-built default options
-p=<config-override-file> (short for -configoverridefile <config-override-file>)
Reads SchemaCrawler configuration properties from
<config-override-file> and overrides the properties from the
configuration file
Optional, defaults to no overrides
--- Output Options ---
-outputformat=<outputformat>
Format of the SchemaCrawler output, where <outputformat> is one of:
text
For text output (default)
html
For XHTML output
csv
For CSV output
dot
For DOT chart format output
Optional, defaults to text
-outputfile=<outputfile>
<outputfile> is the path to the output file
Optional, defaults to the console (stdout)
--- Application Options ---
-loglevel=<loglevel>
Sets the logging granularity, where <loglevel> is one of:
OFF, SEVERE, WARNING, INFO, CONFIG, FINE, FINER, FINEST, ALL
Optional, default OFF
-?, -h, -help, --help
Shows this help text
| [top] |
An example of a SchemaCrawler configuration file.
| [top] |
SchemaCrawler supports any database for which there is a JDBC database driver available. SchemaCrawler has been tested with various databases, and JDBC drivers.
| [top] |
SchemaCrawler works with J2SE 5 and above. SchemaCrawler does not depend on any Java libraries other than the standard J2SE 5 runtime libraries and a JDBC driver. However, J2SE 6 is required for JavaScript scripting using the SchemaCrawler API.
| [top] |
SchemaCrawler works with any operating system that supports J2SE 5 and above.
| [top] |
Yes, SchemaCrawler comes with a full-featured ant task.
See the example in the
examples\ant-task
directory.
| [top] |
Yes, SchemaCrawler comes with a Maven plug-in. You can generate a SchemaCrawler report along with other reports for your Maven generated site.
See the example in the
examples\maven
directory.
| [top] |
SchemaCrawler can generate entity-relationship diagrams with Graphviz . You can filter out tables, columns, and procedures based on regular expressions, using the grep functionality.
| [top] |
An example of a SchemaCrawler database diagram:
| [top] |
SchemaCrawler Grep is a set of SchemaCrawler command-line options that allow you to search your database for tables and columns that match a regular expression, much like the standard grep tool.
| [top] |
SchemaCrawler Grep output is the same as the SchemaCrawler text output.
| [top] |
See the filtering and grep command-line options above.
| [top] |
The SchemaCrawler Grep configuration file is the same as the SchemaCrawler configuration file.
| [top] |
Weak associations are inferred associations between tables, similar to forign keys, even if there is no foreign key defined in the database between the tables. Ruby on Rails table schemes are supported, as well as other simple naming conventions. Table name prefixes are automatically detected. Weak associations are output in the graphs as a dotted line, and are also output in the text formats.
| [top] |
When SchemaCrawler is used with J2SE 6 and above, it has built-in support to be used with JavaScript scripts.
See the example in the
examples\javascript
directory.
| [top] |
SchemaCrawler is an API that improves on the standard JDBC metadata facilities. SchemaCrawler provides an easy to use set of plain old Java objects (POJOs) that represent your database schema.
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 example in the
examples\api
directory.
| [top] |
SchemaCrawler can be integrated with other programs, but this requires some Java programming. SchemaCrawler is
designed to be used programmatically with the
Spring Framework
. Examples are provided in the
examples\spring
directory.
SchemaCrawler comes pre-built with integration with
Apache Velocity.
This allows you to specify your own templates for formatting the schema. Examples are provided in the
examples\velocity
directory.
| [top] |
SchemaCrawler is available as a download from SourceForge .
| [top] |
Technical support is not available for SchemaCrawler. Please post questions on the forums.
| [top] |
SchemaCrawler is free, licensed under the
GNU Lesser General Public License (LGPL),
but
donations
are welcome.
SchemaCrawler is also distributed packaged for different database systems such as
Microsoft SQL Server
,
MySQL
and
PostgreSQL
. These pre-packaged distributions are distributed under the
GNU General Public License (GPL) license.
The JDBC drivers that are packaged with these SchemaCrawler distributions are in their binary form, and retain
their original license.
| [top] |