How do I extract SQL table column type information?

Created May 4, 2012

Lennart Jorelid

Use the getColumns method of the java.sql.DatabaseMetaData interface to investigate the column type information of a particular table. Note that most arguments to the getColumns method (pinpointing the column in question) may be null, to broaden the search criteria. A code sample can be seen 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 column types for the table "sysforeignkeys", in schema
      // "dbo" and catalog "test".
      ResultSet rs = dbmd.getColumns("test", "dbo", "sysforeignkeys", "%");

      // 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 dbColumnName = rs.getString(4);
          String dbColumnTypeName = rs.getString(6);
          int    dbColumnSize = rs.getInt(7);
          int    dbDecimalDigits = rs.getInt(9);
          String dbColumnDefault = rs.getString(13);
          int    dbOrdinalPosition = rs.getInt(17);
          String dbColumnIsNullable = rs.getString(18);

          // Printout
          System.out.println("Col(" + dbOrdinalPosition + "): " + dbColumnName 
                    + " (" + dbColumnTypeName +")");
          System.out.println("   Nullable: " + dbColumnIsNullable + 
                    ", Size: " + dbColumnSize);
          System.out.println("   Position in table: " + dbOrdinalPosition 
                    + ", Decimal digits: " + dbDecimalDigits);
      }

      // Free database resources
      rs.close();
      conn.close();
  }