JDBC Statement – DatabaseMetaData GetTables Example – Developers Corner

Java

Using the DatabaseMetaData interface you can obtain meta data all the tables in the entire database or as is my case for a particular (Catalog and Schema). We will be using the getTables() method, passing in the following four parameters (“tutorial”, “dbo”, “%”, null). If you would prefer to get a list of all tables/views in the entire database then you can pass in null for all four parameters.

Common Methods used with DatabaseMetaData

Method Name	Description
getDriverName()	Retrieves the name of this JDBC driver
getDriverVersion()	Retrieves the version number of this JDBC driver as a String
getDatabaseProductName()	Retrieves the name of this database product
getDatabaseProductVersion()	Retrieves the version number of this database product
getProcedures(String catalog, String schemaPattern, String procedureNamePattern)	Retrieves a description of the stored procedures available in the given catalog
getSchemas(String catalog, String schemaPattern)	Retrieves the schema names available in this database
getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types)	Retrieves a description of the tables available in the given catalog

Using the DatabaseMetaData instance, getTables() Method

DatabaseMetaData metadata = connection.getMetaData();
System.out.println("nList of Tables Available for 'dbo'...n");
ResultSet rs = metadata.getTables("tutorial", "dbo", "%", null);
while (rs.next()) {
  System.out.format("%-15s %-10s %-20s %-10sn", rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4));
}

Full Program Listing

package com.avaldes.tutorials;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCDatabaseMetaDataTablesExample {

  public static void main(String[] args)  throws SQLException {
    String database_url   = "jdbc:sqlserver://localhost:1433;databaseName=tutorial";
    String username     = "webuser";
    String password     = "webuser123";
    Connection connection   = null;
    
    try {
      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    } catch (ClassNotFoundException e) {
      System.out.println("ERROR: Unable to load SQLServer JDBC Driver");
      e.printStackTrace();
      return;
    }
   
    try {
      connection = DriverManager.getConnection(database_url, username, password);
    } catch (SQLException e) {
      System.out.println("ERROR:  Unable to establish a connection with the database!");
      e.printStackTrace();
      return;
    }
   
    try {
      if (connection != null) {
        DatabaseMetaData metadata = connection.getMetaData();
          System.out.println("nList of Tables Available for 'dbo'...n");
          System.out.format("%-15s %-10s %-20s %-10sn", "DATABASE", "SCHEMA", "TABLE NAME", "TYPE");
          System.out.format("%-15s %-10s %-20s %-10sn", "-------------", "--------", "----------------", "-----");
          ResultSet rs = metadata.getTables("tutorial", "dbo", "%", null);
          while (rs.next()) {
            System.out.format("%-15s %-10s %-20s %-10sn", rs.getString(1), rs.getString(2), 
                      rs.getString(3), rs.getString(4));
          }
          
      } else {
        System.out.println("ERROR: Unable to make a database connection!");
      }
      
      
    } catch (SQLException e) {
      e.printStackTrace();
      return;
    } finally {
        if (connection != null) connection.close();
    }
  }
}

Output

List of Tables Available for 'dbo'...

DATABASE        SCHEMA     TABLE NAME           TYPE      
-------------   --------   ----------------     -----     
tutorial        dbo        address              TABLE     
tutorial        dbo        employee             TABLE     
tutorial        dbo        securities           TABLE     
tutorial        dbo        user_preference      TABLE