How can I investigate the physical structure of a database?

Created Nov 21, 1999

Lennart Jorelid

The JDBC view of a database internal structure can be seen in the image below.

Several database objects (tables, views, procedures etc.) are contained within a Schema.
Several schema (user namespaces) are contained within a catalog.
Several catalogs (database partitions; databases) are contained within a DB server (such as Oracle, MS SQL

The JDBC view of a database internal structure.

The DatabaseMetaData interface has methods for discovering all the Catalogs, Schemas, Tables and Stored Procedures in the database server. The methods are pretty intuitive, returning a ResultSet with a single String column; use them as indicated in the code below:

public static void main(String[] args) throws Exception
{
      // Load the database driver - in this case, we
      // use the Jdbc/Odbc bridge driver.
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

      // Open a connection to the database
      Connection conn = DriverManager.getConnection("[jdbcURL]", 
                 "[login]", "[passwd]");

      // Get DatabaseMetaData
      DatabaseMetaData dbmd = conn.getMetaData();

      // Get all Catalogs
      System.out.println("
Catalogs are called '" + dbmd.getCatalogTerm() 
            + "' in this RDBMS.");
      processResultSet(dbmd.getCatalogTerm(), dbmd.getCatalogs());

      // Get all Schemas
      System.out.println("
Schemas are called '" + dbmd.getSchemaTerm()
            + "' in this RDBMS.");
      processResultSet(dbmd.getSchemaTerm(), dbmd.getSchemas());

      // Get all Table-like types
      System.out.println("
All table types supported in this RDBMS:");
      processResultSet("Table type", dbmd.getTableTypes());

      // Close the Connection
      conn.close();
  }

  public static void processResultSet(String preamble, ResultSet rs) 
         throws SQLException
  {
      // Printout table data
      while(rs.next())
      {
          // Printout
          System.out.println(preamble + ": " + rs.getString(1));
      }

      // Close database resources
      rs.close();
}