Listing All Tables - getTables()

JDBC Tutorials - Herong's Tutorial Notes

Dr. Herong Yang, Version 2.11

JDBC Tutorials - Herong's Tutorial Notes

∟Microsoft JDBC Driver - DatabaseMetaData Object

∟Listing All Tables - getTables()

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

Table of Contents

About This Book

JDBC (Java Database Connectivity) Introduction

Downloading and Installing JDK - Java SE

Installing and Running Java DB - Derby

Derby (Java DB) JDBC Driver

Derby (Java DB) JDBC DataSource Objects

Java DB (Derby) - DML Statements

Java DB (Derby) - ResultSet Objects of Queries

Java DB (Derby) - PreparedStatement

MySQL Installation on Windows

MySQL JDBC Driver (MySQL Connector/J)