sql

List all database table names

This is an example of how to list all database table names. Listing all database table names implies that you should:

  • Load the JDBC driver, using the forName(String className) API method of the Class. In this example we use the MySQL JDBC driver.
  • Create a Connection to the database. Invoke the getConnection(String url, String user, String password) API method of the DriverManager to create the connection.
  • Create a DatabaseMetaData, using the getMetaData() API method of the Connection. The metadata includes information about the database’s tables, its supported SQL grammar, its stored procedures, the capabilities of this connection.
  • Invoke the getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types) API method of the DatabaseMetaData. The parameters catalog and schemaPattern should be set to null, the tableNamePattern should be “%” and the types should be “TABLE”, since we want to get information about the tables. The result is a ResultSet with a description of the tables available in the given catalog. For each result in the ResultSet get the tablename, the catalog and the schema it belongs to, with the getString(int columnIndex) AI method of the ResultSet.

Let’s take a look at the code snippet that follows:

package com.javacodegeeks.snippets.core;

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

public class GetAllTableNames {
 
  public static void main(String[] args) {

    Connection connection = null;
    try {

  // Load the MySQL JDBC driver

  String driverName = "com.mysql.jdbc.Driver";

  Class.forName(driverName);


  // Create a connection to the database

  String serverName = "localhost";

  String schema = "test";

  String url = "jdbc:mysql://" + serverName +  "/" + schema;

  String username = "username";

  String password = "password";

  connection = DriverManager.getConnection(url, username, password);

  

  System.out.println("Successfully Connected to the database!");

  
    } catch (ClassNotFoundException e) {

  System.out.println("Could not find the database driver " + e.getMessage());
    } catch (SQLException e) {

  System.out.println("Could not connect to the database " + e.getMessage());
    }

    try {

  // Get the database metadata

  DatabaseMetaData metadata = connection.getMetaData();


  // Specify the type of object; in this case we want tables

  String[] types = {"TABLE"};

  ResultSet resultSet = metadata.getTables(null, null, "%", types);


  while (resultSet.next()) {

    String tableName = resultSet.getString(3);

    String tableCatalog = resultSet.getString(1);

    String tableSchema = resultSet.getString(2);


    System.out.println("Table : " + tableName + "nCatalog : " + tableCatalog + "nSchema : " + tableSchema);

  }
    } catch (SQLException e) {

  System.out.println("Could not get database metadata " + e.getMessage());
    }

  }
}

Example Output:

Successfully Connected to the database!
Table : test_table
Catalog : test
Schema : null

 
This was an example of how to list all database table names in Java.

Ilias Tsagklis

Ilias is a software developer turned online entrepreneur. He is co-founder and Executive Editor at Java Code Geeks.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button