How do I check what table-like database objects (table, view, temporary table, alias) are present in a particular database?

Lennart Jorelid

Use java.sql.DatabaseMetaData to probe the database for metadata. Use the getTables method to retrieve information about all database objects (i.e. tables, views, system tables, temporary global or local tables or aliases). The exact usage is described in the code below.

NOTE! Certain JDBC drivers throw IllegalCursorStateExceptions when you try to access fields in the ResultSet in the wrong order (i.e. not consecutively). Thus, you should not change the order in which you retrieve the metadata from the ResultSet.

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 dbObjects. Replace the last argument in the getTables
      // method with objectCategories below to obtain only database 
      // tables. (Sending in null retrievs all dbObjects).
      String[] objectCategories = {"TABLE"};
      ResultSet rs = dbmd.getTables(null, null, "%", null);

      // Printout table data
      while(rs.next())
      {
          // Get dbObject metadata
          String dbObjectCatalog = rs.getString(1);
          String dbObjectSchema = rs.getString(2);
          String dbObjectName = rs.getString(3);
          String dbObjectType = rs.getString(4);

          // Printout
          System.out.println("" + dbObjectType + ": " + dbObjectName);
          System.out.println("   Catalog: " + dbObjectCatalog);
          System.out.println("   Schema: " + dbObjectSchema);
      }

      // Close database resources
      rs.close();
      conn.close();
}
0 Comments  (click to add your comment)
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

About | Sitemap | Contact