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(); } |