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, 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.

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.
 */
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
    {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      Connection con = DriverManager.getConnection("jdbc:odbc:DB_NAME", "", "");
      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.