Extensions with Catalog Attributes

SchemaCrawler can read and incorporate user-provided metadata in the form of table, column and foreign key remarks and attributes, and implicit association definitions, and alternate key specifications from a YAML file. Examples of attributes file are shown below. Then you can run SchemaCrawler with a --attributes-file <path> command-line option.

SchemaCrawler needs Jackson jars on the classpath to read the YAML file, and these provided with the SchemaCrawler download.

Adding Table and Column Remarks

You can read and incorporate table and column remarks into the SchemaCrawler schema by creating a file like the one below. These remarks will be show in SchemaCrawler output. SchemaCrawler does not allow you to provide remarks for external tables or columns.

name: catalog
tables:
- catalog: PUBLIC
  schema: BOOKS
  name: AUTHORS
  remarks:
  - Overwritten remarks line 1
  columns:
  - name: FIRSTNAME
    remarks:
    - Overwritten remarks line 1
    - Overwritten remarks line 2
    attributes:
      tag1: tagvalue1
  - name: LASTNAME
    remarks:
    - Overwritten remarks line 1

If your JDBC driver does not support catalogs, you can omit the “catalog” key, and if it does not support schemas, you can omit the “schema” key.

Creating Implicit Associations

You can create implicit associations between columns of two tables (or even the same table) by creating a file like the one below. These implicit associations will be shown in SchemaCrawler output and diagrams.

Implicit Associations can be between columns of tables of the SchemaCrawler schema and columns in external schemas. This is useful for tracking data lineage or other types of associations. You can provide remarks for implicit associations which will also be shown in SchemaCrawler output. SchemaCrawler does not allow you to provide column references between two external tables.

Also see more information on how SchemaCrawler can infer implicit associations.

name: catalog
implicit-associations:
- name: multi_line_remarks
  referenced-table:
    catalog: PUBLIC
    schema: BOOKS
    name: AUTHORS
  referencing-table:
    catalog: PUBLIC
    schema: BOOKS
    name: BOOKS
  column-references:
    ID: ID
  remarks:
  - "Some remarks line 1"
  - "Some remarks line 2"
- name: multi_remarks_reference
  referenced-table:
    catalog: PUBLIC
    schema: PUBLISHER SALES
    name: SALES
  referencing-table:
    catalog: PRIVATE
    schema: ALLSALES
    name: REGIONS
  column-references:
    POSTALCODE: POSTALCODE
    COUNTRY: COUNTRY
  remarks:
  - "Other remarks line 1"
  - "Other remarks line 2"

Adding Foreign Key Remarks

You can annotate foreign keys in your schema with remarks. These remarks can be shown in SchemaCrawler diagrams and in other SchemaCrawler output. Use syntax like the one above, still using the “implicit-associations” key. SchemaCrawler will find a foreign key match for the columns in the referenced and referencing tables, and will update the remarks on that foreign key.

For a hands-on walkthrough, see the attributes example.