Listing All Tables - getTables()

JDBC for SQL Server - Herong's Tutorial Examples

∟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) HerongYang.com. All Rights Reserved.
 */
import java.sql.*;
public class ListTables {
  public static void main(String [] args) {
    Connection con = null;
    try {

      con = DriverManager.getConnection(
          "jdbc:sqlserver://localhost\\SQLEXPRESS;"
        + "user=herong;password=T0pSecret;"
        + "database=AdventureWorks2019");

      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 (Exception e) {
      e.printStackTrace();
    }
  }
}

If you run this program, you will get:

herong> java -cp .;mssql-jdbc-9.4.1.jre16.jar ListTables.java

List of tables:
   ..., dbo, AWBuildVersion,...
   ..., dbo, DatabaseLog,...
   ..., dbo, ErrorLog,...
   ..., HumanResources, Department,...
   ..., HumanResources, Employee,...
   ..., HumanResources, EmployeeDepartmentHistory,...
   ..., HumanResources, EmployeePayHistory,...
   ..., HumanResources, JobCandidate,...
   ..., HumanResources, Shift,...
   ..., Person, Address,...
   ..., Person, AddressType,...
   ..., Person, BusinessEntity,...
   ..., Person, BusinessEntityAddress,...
   ..., Person, BusinessEntityContact,...
   ..., Person, ContactType,...
   ..., Person, CountryRegion,...
   ..., Person, EmailAddress,...
   ..., Person, Password,...
   ..., Person, Person,...
   ..., Person, PersonPhone,...
   ..., Person, PhoneNumberType,...
   ..., Person, StateProvince,...
   ..., Production, BillOfMaterials,...
   ..., Production, Culture,...
   ..., Production, Document,...
   ..., Production, Illustration,...
   ..., Production, Location,...
   ..., Production, Product,...
   ..., Production, ProductCategory,...
   ..., Production, ProductCostHistory,...
   ..., Production, ProductDescription,...
   ..., Production, ProductDocument,...
   ..., Production, ProductInventory,...
   ..., Production, ProductListPriceHistory,...
   ..., Production, ProductModel,...
   ..., Production, ProductModelIllustration,...
   ..., Production, ProductModelProductDescriptionCulture,...
   ..., Production, ProductPhoto,...
   ..., Production, ProductProductPhoto,...
   ..., Production, ProductReview,...
   ..., Production, ProductSubcategory,...
   ..., Production, ScrapReason,...
   ..., Production, TransactionHistory,...
   ..., Production, TransactionHistoryArchive,...
   ..., Production, UnitMeasure,...
   ..., Production, WorkOrder,...
   ..., Production, WorkOrderRouting,...
   ..., Purchasing, ProductVendor,...
   ..., Purchasing, PurchaseOrderDetail,...
   ..., Purchasing, PurchaseOrderHeader,...
   ..., Purchasing, ShipMethod,...
   ..., Purchasing, Vendor,...
   ..., Sales, CountryRegionCurrency,...
   ..., Sales, CreditCard,...
   ..., Sales, Currency,...
   ..., Sales, CurrencyRate,...
   ..., Sales, Customer,...
   ..., Sales, PersonCreditCard,...
   ..., Sales, SalesOrderDetail,...
   ..., Sales, SalesOrderHeader,...
   ..., Sales, SalesOrderHeaderSalesReason,...
   ..., Sales, SalesPerson,...
   ..., Sales, SalesPersonQuotaHistory,...
   ..., Sales, SalesReason,...
   ..., Sales, SalesTaxRate,...
   ..., Sales, SalesTerritory,...
   ..., Sales, SalesTerritoryHistory,...
   ..., Sales, ShoppingCartItem,...
   ..., Sales, SpecialOffer,...
   ..., Sales, SpecialOfferProduct,...
   ..., Sales, Store,...
   ..., sys, trace_xe_action_map,...
   ..., sys, trace_xe_event_map,...