Match Table Name and Alias at runtime from DatabaseMetaData?

Question

I know how to use .getTables() to get the TABLE and ALIAS entries.

final ResultSet rs = dmd.getTables(null, "MySchema", "%", new String[]{"TABLE","ALIAS"});

What I need to be able to determine is which ALIAS goes with which TABLE name?

I specifically need to be able to do this on Oracle, SQL Server and DB2.

And given I have a table T00001 and it has an alias of MyTable how can I match the two up using the DatabaseMetaData from a Connection.getMetaData() call?

I don't think it's possible with plain JDBC methods. Any chances you could restrict it to a specific DBMS product? There you usually have more options to look inside the DB schema — André Schild, Jul 11 '15 at 14:02
Which DB are you talking about? I am afraid this can't be done in pure JDBC. — jakub.petr, Jul 11 '15 at 14:02

score 1 · Accepted Answer · answered Jul 12 '15 at 23:36

After reviewing the JDBC APIs and oracle.jdbc.OracleDatabaseMetaData, I'm pretty certain that there is no way to retrieve this information directly through DatabaseMetaData when using stock Oracle JDBC drivers. There may be driver-specific ways for DB2 or SQL Server as I didn't check them out thoroughly. As a cheat, you can getConnection() from the DatabaseMetaData object and run the database-specific queries to retrieve the information you need, as follows:

public class TableAliasMapperJdbc {
    public Map<String, List<String>> mapTableAliases(String url, String user, String password, String sql) throws SQLException {
        try (
            Connection conn = DriverManager.getConnection(url, user, password);
            // we use conn.getMetaData().getConnection() instead of conn here only to fit within the parameters of the question
            PreparedStatement stmt = conn.getMetaData().getConnection().prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();
        ) {
            // this may not do what you want if you have synonyms of synonyms
            Map<String, List<String>> tableAliases = new HashMap<>();
            while (rs.next()) {
                String table = rs.getString(1);
                String alias = rs.getString(2);
                List<String> aliases = tableAliases.get(table);
                if (aliases == null) {
                    tableAliases.put(table, aliases = new ArrayList<>(2));
                }
                aliases.add(alias);
            }
            return tableAliases;
        }
    }

    public void print(String dbName, Map<String, List<String>> tableAliases) {
        System.out.format("\nThe following are the table aliases for %s:\n", dbName);
        for (Map.Entry<String, List<String>> entry : tableAliases.entrySet()) {
            System.out.format("The alias(es) for %s are: %s.\n", entry.getKey(), String.join(", ", entry.getValue()));
        }
    }

    public static void main(String[] args) throws SQLException {
        TableAliasMapperJdbc mapper = new TableAliasMapperJdbc();
        mapper.print("Oracle",
            mapper.mapTableAliases(
                "jdbc:oracle:thin:@localhost:1521:xe",
                "scott",
                "tiger",
                "SELECT table_name, synonym_name FROM user_synonyms")); // or maybe all_synonyms

        mapper.print("DB2",
            mapper.mapTableAliases(
                "jdbc:db2://localhost:50000/SAMPLE",
                "db2admin",
                "db2admin",
                "SELECT base_tabname, tabname FROM syscat.tables WHERE type = 'A' AND owner = 'DB2ADMIN'"));

        mapper.print("SQL Server",
            mapper.mapTableAliases(
                "jdbc:sqlserver://localhost:1433",
                "sa",
                "Password123",
                "SELECT PARSENAME(base_object_name,1), name FROM sys.synonyms"));
    }
}

Code was successfully tested using JDK 1.8.0_45, Oracle XE 11.2.0.2.0 and its bundled JDBC driver, DB2 Express-C 10050500 and its bundled JDBC driver, and SQL Server 2014 Express 12.0.2000.8 with Microsoft JDBC Driver 4.1.5605.100.

_{SQL Server query based on http://sqlblog.com/blogs/john_paul_cook/archive/2010/08/24/script-to-list-synonym-contents.aspx.}

Match Table Name and Alias at runtime from DatabaseMetaData?

1 Answers1