How can I investigate the physical structure of a database?
Created May 4, 2012
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(); } |