Java Program to Get All The Tables in a DB Schema

In this post we’ll see a Java program to get all the tables in a schema in a DB. Database used here is MySQL.

For listing DB schema tables in Java you can use getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types) method provided by the DatabaseMetaData interface in the JDBC API. You can provide null as value for all the parameters, that way you don’t narrow the search and all the tables are returned. If you want to narrow your search to get specific tables then you can provide values for these parameters.

Listing DB schema tables Java example

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

public class DBMetaData {
  public static void main(String[] args) {
    Connection connection = null;
    try {
      // Loading driver
      Class.forName("com.mysql.jdbc.Driver");

      // Creating connection
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/world", 
                          "root", "admin");
      // Getting DatabaseMetaData object
      DatabaseMetaData dbMetaData = connection.getMetaData();
     
      ResultSet rs = dbMetaData.getTables(null, null, null, null);       
      
      while (rs.next()){
        System.out.println(""Table name - " " + rs.getString(3));
      }    
    } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally{
      if(connection != null){
        //closing connection 
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      } // if condition
    }// finally
  }
}

Points to note here are-

Here connection is made to the “world” schema in the MySQL DB ( jdbc:mysql://localhost:3306/world) so program will list all the table names in the world schema.
Returned resultset has table description rows where each row has following columns -

**Table Descrition Columns**
Column Name	Type	Description
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" etc.
REMARKS	String	explanatory comment on the table (may be null)
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.

That’s why column index is 3 while getting result from ResultSet in the Java code as TABLE_NAME is at number 3.

That's all for this topic Java Program to Get All The Tables in a DB Schema. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Java Programs Page

Related Topics

You may also like-

Tech Tutorials

Saturday, June 19, 2021

Java Program to Get All The Tables in a DB Schema

Listing DB schema tables Java example