Listing All Culumns - getColumns()

<< Microsoft JDBC Driver - DatabaseMetaData Object

<< JDBC Tutorials - Herong's Tutorial Notes

This section describes how to get a list of all columns in a table or a view through the DatabaseMetaData object.

If you want list all columns in a table, you can use the DatabaseMetaData method: getColumns(). Input parameters are:

• catalog - a catalog name; must match the catalog name as it is stored in the database; "" retrieves those without a catalog; null means that the catalog name should not be used to narrow the search
• schemaPattern - a schema name pattern; must match the schema name as it is stored in the database; "" retrieves those without a schema; null means that the schema name should not be used to narrow the search
• tableNamePattern - a table name pattern; must match the table name as it is stored in the database
• columnNamePattern - a column name pattern; must match the column name as it is stored in the database

The output is captured in a ResultSet object with the following fields:

• TABLE_CAT String => table catalog (may be null)
• TABLE_SCHEM String => table schema (may be null)
• TABLE_NAME String => table name
• COLUMN_NAME String => column name
• DATA_TYPE int => SQL type from java.sql.Types
• TYPE_NAME String => Data source dependent type name, for a UDT the type name is fully qualified
• COLUMN_SIZE int => column size.
• DECIMAL_DIGITS int => the number of fractional digits. Null is returned for data types where DECIMAL_DIGITS is not applicable.
• NULLABLE int => is NULL allowed.

The following sample program displays all columns in the Customer table:

/**
 * ListColumns.java
 * Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
 */
import java.sql.*;
public class ListColumns {
  public static void main(String [] args) {
    Connection con = null;
    try {

      Class.forName(
        "com.microsoft.sqlserver.jdbc.SQLServerDriver");
      con = DriverManager.getConnection(
          "jdbc:sqlserver://localhost:1269;"
        + "user=sa;password=HerongYang;"
        + "database=AdventureWorksLT");

      DatabaseMetaData meta = con.getMetaData();
      ResultSet res = meta.getColumns(null, null, "Customer", null);
      System.out.println("List of columns: "); 
      while (res.next()) {
         System.out.println(
           "  "+res.getString("TABLE_SCHEM")
           + ", "+res.getString("TABLE_NAME")
           + ", "+res.getString("COLUMN_NAME")
           + ", "+res.getString("TYPE_NAME")
           + ", "+res.getInt("COLUMN_SIZE")
           + ", "+res.getString("NULLABLE")); 
      }
      res.close();

      con.close();
    } catch (java.lang.ClassNotFoundException e) {
      System.err.println("ClassNotFoundException: "
        +e.getMessage());
    } catch (SQLException e) {
      System.err.println("SQLException: "
        +e.getMessage());
    }
  }
}

If you run this program, you will get:

List of columns:
  SalesLT, Customer, CustomerID, int identity, 10, 0
  SalesLT, Customer, NameStyle, NameStyle, 1, 0
  SalesLT, Customer, Title, nvarchar, 8, 1
  SalesLT, Customer, FirstName, Name, 50, 0
  SalesLT, Customer, MiddleName, Name, 50, 1
  SalesLT, Customer, LastName, Name, 50, 0
  SalesLT, Customer, Suffix, nvarchar, 10, 1
  SalesLT, Customer, CompanyName, nvarchar, 128, 1
  SalesLT, Customer, SalesPerson, nvarchar, 256, 1
  SalesLT, Customer, EmailAddress, nvarchar, 50, 1
  SalesLT, Customer, Phone, Phone, 25, 1
  SalesLT, Customer, PasswordHash, varchar, 128, 0
  SalesLT, Customer, PasswordSalt, varchar, 10, 0
  SalesLT, Customer, rowguid, uniqueidentifier, 36, 0
  SalesLT, Customer, ModifiedDate, datetime, 23, 0

Sections in This Chapter

Commonly Used DatabaseMetaData Methods

Getting Database Server and Driver Info

Listing All Databases - getCatalogs()

Listing All Schemas - getSchemas()

Listing All Tables - getTables()

Listing All Culumns - getColumns()

Listing All Stored Procedures - getProcedures()