SchemaCrawler Issue #166

Issue166.java

package schemacrawler.issues;


import static us.fatehi.commandlineparser.CommandLineUtility.applyApplicationLogLevel;
import static us.fatehi.commandlineparser.CommandLineUtility.logSystemProperties;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.logging.Level;

import javax.sql.DataSource;

import schemacrawler.crawl.SchemaCrawler;
import schemacrawler.schema.Catalog;
import schemacrawler.schema.Column;
import schemacrawler.schema.Schema;
import schemacrawler.schema.Table;
import schemacrawler.schemacrawler.DatabaseConnectionOptions;
import schemacrawler.schemacrawler.DatabaseSpecificOverrideOptions;
import schemacrawler.schemacrawler.DatabaseSpecificOverrideOptionsBuilder;
import schemacrawler.schemacrawler.RegularExpressionInclusionRule;
import schemacrawler.schemacrawler.SchemaCrawlerException;
import schemacrawler.schemacrawler.SchemaCrawlerOptions;
import schemacrawler.schemacrawler.SchemaInfoLevel;
import schemacrawler.schemacrawler.SchemaInfoLevelBuilder;
import schemacrawler.utility.SchemaCrawlerUtility;

public final class Issue166
{

  public static void main(final String[] args)
    throws Exception
  {
    // Turn application logging on by applying the correct log level
    applyApplicationLogLevel(Level.OFF);
    // Log system properties and classpath
    logSystemProperties();

    // Create the options
    final SchemaCrawlerOptions options = new SchemaCrawlerOptions();
    // Set what details are required in the schema - this affects the
    // time taken to crawl the schema
    final SchemaInfoLevel infoLevel = SchemaInfoLevelBuilder.standard();
    infoLevel.setRetrieveAdditionalTableAttributes(true);
    infoLevel.setRetrieveAdditionalColumnAttributes(true);
    options.setSchemaInfoLevel(infoLevel);
    options
      .setSchemaInclusionRule(new RegularExpressionInclusionRule("BOOKS.dbo"));

    final Connection connection = getConnection();
    final DatabaseSpecificOverrideOptionsBuilder dbSpecificBldr = SchemaCrawlerUtility
      .buildDatabaseSpecificOverrideOptions(connection);
    dbSpecificBldr.withInformationSchemaViews()
      .withAdditionalTableAttributesSql("SELECT                                             \n"
                                        + "  DB_NAME() AS TABLE_CATALOG,                      \n"
                                        + "  SCHEMA_NAME(ST.SCHEMA_ID) AS TABLE_SCHEMA,       \n"
                                        + "  ST.NAME AS TABLE_NAME,                           \n"
                                        + "  SEP.VALUE AS REMARKS                             \n"
                                        + "FROM                                               \n"
                                        + "  SYS.TABLES ST                                    \n"
                                        + "  INNER JOIN SYS.EXTENDED_PROPERTIES SEP           \n"
                                        + "    ON ST.OBJECT_ID = SEP.MAJOR_ID                 \n"
                                        + "    AND SEP.MINOR_ID = 0                           \n"
                                        + "    AND SEP.NAME = 'MS_DESCRIPTION'                ")
      .withAdditionalColumnAttributesSql("SELECT                                               \n"
                                         + "  DB_NAME() AS TABLE_CATALOG,                        \n"
                                         + "  SCHEMA_NAME(ST.SCHEMA_ID) AS TABLE_SCHEMA,         \n"
                                         + "  ST.NAME AS TABLE_NAME,                             \n"
                                         + "  SC.NAME AS COLUMN_NAME,                            \n"
                                         + "  SEP.VALUE AS REMARKS                               \n"
                                         + "FROM                                                 \n"
                                         + "  SYS.TABLES ST                                      \n"
                                         + "  INNER JOIN SYS.COLUMNS SC                          \n"
                                         + "    ON ST.OBJECT_ID = SC.OBJECT_ID                   \n"
                                         + "  INNER JOIN SYS.EXTENDED_PROPERTIES SEP             \n"
                                         + "    ON ST.OBJECT_ID = SEP.MAJOR_ID                   \n"
                                         + "    AND SC.COLUMN_ID = SEP.MINOR_ID                  \n"
                                         + "    AND SEP.NAME = 'MS_DESCRIPTION'                  ");

    // Get the schema definition
    final DatabaseSpecificOverrideOptions dbSpecificOptions = dbSpecificBldr
      .toOptions();
    final SchemaCrawler schemaCrawler = new SchemaCrawler(connection,
                                                          dbSpecificOptions);
    final Catalog catalog = schemaCrawler.crawl(options);

    for (final Schema schema: catalog.getSchemas())
    {
      System.out.println(schema);
      for (final Table table: catalog.getTables(schema))
      {
        System.out.println("o--> " + table + " \""
                           + table.getAttribute("REMARKS") + "\"");

        for (final Column column: table.getColumns())
        {
          System.out.println("     o--> " + column + " \""
                             + column.getAttribute("REMARKS") + "\"");
        }
      }
    }

  }

  private static Connection getConnection()
    throws SchemaCrawlerException, SQLException
  {
    final String connectionUrl = "jdbc:sqlserver://scsqlserver.cdf972bn8znp.us-east-1.rds.amazonaws.com:1433;databaseName=BOOKS";
    final DataSource dataSource = new DatabaseConnectionOptions(connectionUrl);
    return dataSource.getConnection("schemacrawler", "schemacrawler");
  }

}