Saturday, June 19, 2021

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

      // 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 ({
        System.out.println(""Table name - " " + rs.getString(3));
    } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      if(connection != null){
        //closing connection 
        try {
        } catch (SQLException e) {
          // TODO Auto-generated catch block
      } // 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 NameTypeDescription
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

  1. Connection Pooling Using C3P0 in Java
  2. Connection Pooling Using Apache DBCP in Java
  3. Java Program to Get All DB Schemas
  4. DataSource in Java-JDBC
  5. CallableStatement Interface in Java-JDBC

You may also like-

  1. Count Total Number of Times Each Character Appears in a String - Java Program
  2. Add Double Quotes to a String Java Program
  3. How to Run a Shell Script From Java Program
  4. Difference Between Two Dates in Java
  5. Interface Default Methods in Java
  6. Try-With-Resources in Java With Examples
  7. Java ThreadLocal Class With Examples
  8. Method Reference in Java