Java JDBC metadata example - A program to search for a given field name in all database tables in a database

Note: You can use the Java program below for this task, especially if you’re not using MySQL, but if you are using MySQL, you can use this query instead:

SELECT DISTINCT TABLE_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('field_photo', 'field_photo_alt')
        AND TABLE_SCHEMA='mydatabase';

That query searches a MySQL database named mydatabase for the two field names show (field_photo, field_photo_alt). I find that query on this SO page.

If you’re not using MySQL, the following Java program may help ...

Find all tables with a given column name using Java

This is probably not the most commonly needed piece of Java code, but I've run into a situation where I have to work with a large database that for some currently-unknown reason has no foreign keys declared between tables, even though there are obviously relationships that should be defined with foreign keys. So, what I've done to partially combat this problem is write a Java program that goes through each table in the database, uses the database table metadata, and if it finds a field with the name I've specified, it prints out the name of the database table where the field was found. As hokey as this sounds, it has actually helped me find a few previously-unknown relationships in this database of 150+ tables.

If you ever need to find all tables in a database, or otherwise search through the fields of all database tables, feel free to use this program to help you get started. BTW, a few names have been changed to protect the innocent (guilty?).

Also, FWIW, as you can see from the driver and URL that I'm using, I'm searching a Microsoft Access database, and I use the JDBC ODBC driver to connect to the Access database.

Without any further introduction, here is the one-class program.

package my_package;

import java.sql.*;
import java.util.*;

/**
 * Use this program to search the entire database for a given column name.
 * The expected purpose of this program is to help find foreign keys that are not identified
 * in the schema.
 * Currently configured to search a Microsoft Access database.
 */
public class Main_SearchAllTablesForFieldname {

  // put the desired database field name here.
  // the program will search all tables in the database for this name.
  String colNameToSearchFor = "part_no";
  
  String catalog = null;
  String schema = null;
  List listOfTables = new ArrayList();

  public static void main(String[] args)
  {
    new Main_SearchAllTablesForFieldname();
  }

  public Main_SearchAllTablesForFieldname()
  {
    try
    {
      // connect with the jdbc odbc bridge driver
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

      // the jdbc odbc connection string
      Connection con = DriverManager.getConnection("jdbc:odbc:DB_NAME", "", "");

      // get the database metadata
      DatabaseMetaData dmd = con.getMetaData();

      // get a list of all tables
      getListOfAllTables(listOfTables, dmd);

      // see if you can find the column name in any tables
      searchForColumnNameInTables(dmd);
    }
    catch (Exception e)
    {
      System.err.println("exception: " + e.getMessage());
    }
  }

  private void searchForColumnNameInTables(DatabaseMetaData dmd) 
  throws SQLException {
    Iterator iter = listOfTables.iterator();
    while (iter.hasNext()) {
      String tableName = (String) iter.next();
      java.sql.ResultSet rs = dmd.getColumns(catalog, schema, tableName, "%");
      while (rs.next()) {
        String colName = rs.getString(4);
        if (colName.trim().toLowerCase().equals(colNameToSearchFor)){
          System.out.println("found '" + colNameToSearchFor + "' in " + tableName );
        }
      }
    }
  }

  private void getListOfAllTables(List listOfTables, DatabaseMetaData dmd) 
  throws SQLException {
    String[] tableTypes = {
        "TABLE",
        "VIEW",
        "ALIAS",
        "SYNONYM",
        "GLOBAL TEMPORARY",
        "LOCAL TEMPORARY",
        "SYSTEM TABLE"};
    ResultSet rs = dmd.getTables(catalog, schema, "%", tableTypes);

    while (rs.next()) {
      String tableName = rs.getString(3);
      listOfTables.add(tableName);
    }
    rs.close();
  }

}

I'll probably improve this program to let me use regular expressions in my search, but for the moment it does what it is designed to do. Now, as to why foreign keys were not used in this large database ... that remains a mystery.

Add new comment

Anonymous format

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul type> <ol start type> <li> <pre>
  • Lines and paragraphs break automatically.