Question - How Can I Investigate The Physical Structure Of A Database?
Answer -
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 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("nCatalogs are called '" + dbmd.getCatalogTerm()
+ "' in this RDBMS.");
processResultSet(dbmd.getCatalogTerm(), dbmd.getCatalogs());
// Get all Schemas
System.out.println("nSchemas are called '" + dbmd.getSchemaTerm()
+ "' in this RDBMS.");
processResultSet(dbmd.getSchemaTerm(), dbmd.getSchemas());
// Get all Table-like types
System.out.println("nAll 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();
}