Listing All Tables - getTables()

<< Microsoft JDBC Driver - DatabaseMetaData Object

<< JDBC Tutorials - Herong's Tutorial Notes

This section describes how to get a list of all tables in the current database on the SQL Server through the DatabaseMetaData object.

If you want to list all tables in the current database on the SQL server, you can use the DatabaseMetaData method: getTables(). It returns all tables that meets the input criteria specified. 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
• types - a list of table types, which must be from the list of table types returned from getTableTypes(); null returns all types

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
• TABLE_TYPE String => table type. Typical types are "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
• REMARKS String => explanatory comment on the table
• TYPE_CAT String => the types catalog (may be null)
• TYPE_SCHEM String => the types schema (may be null)
• TYPE_NAME String => type name (may be null)
• SELF_REFERENCING_COL_NAME String => name of the designated "identifier" column of a typed table (may be null)
• REF_GENERATION String => specifies how values in SELF_REFERENCING_COL_NAME are created. Values are "SYSTEM", "USER", "DERIVED". (may be null)

The following sample program displays all tables in the AdventureWorksLT database:

/**
 * ListTables.java
 * Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
 */
import java.sql.*;
public class ListTables {
  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.getTables(null, null, null, 
         new String[] {"TABLE"});
      System.out.println("List of tables: "); 
      while (res.next()) {
         System.out.println(
            "   "+res.getString("TABLE_CAT") 
           + ", "+res.getString("TABLE_SCHEM")
           + ", "+res.getString("TABLE_NAME")
           + ", "+res.getString("TABLE_TYPE")
           + ", "+res.getString("REMARKS")); 
      }
      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 tables:
   AdventureWorksLT, dbo, BuildVersion, TABLE, null
   AdventureWorksLT, dbo, ErrorLog, TABLE, null
   AdventureWorksLT, SalesLT, Address, TABLE, null
   AdventureWorksLT, SalesLT, Customer, TABLE, null
   AdventureWorksLT, SalesLT, CustomerAddress, TABLE, null
   AdventureWorksLT, SalesLT, Product, TABLE, null
   AdventureWorksLT, SalesLT, ProductCategory, TABLE, null
   AdventureWorksLT, SalesLT, ProductDescription, TABLE, null
   AdventureWorksLT, SalesLT, ProductModel, TABLE, null
   AdventureWorksLT, SalesLT, ProductModelProductDescription, TABL...
   AdventureWorksLT, SalesLT, SalesOrderDetail, TABLE, null
   AdventureWorksLT, SalesLT, SalesOrderHeader, TABLE, null

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