How to get all table names from a database?

Question

I'd like to retrieve all table names from a database schema, and, if possible, get all table starting with a specified prefix.

I tried using JDBC's connection.getMetaData().getTables() but it didn't work at all.

Connection jdbcConnection = DriverManager.getConnection("", "", "");
DatabaseMetaData m = jdbcConnection.getMetaData();
ResultSet tables = m.getTables(jdbcConnection.getCatalog(), null, "TAB_%", null);
for (int i = 0; i < tables.getMetaData().getColumnCount(); i++) {
   System.out.println("table = " + tables.getMetaData().getTableName(i));
}

Could someone help me on this?

score 145 · Accepted Answer · edited Mar 21 '17 at 05:11

145

You need to iterate over your ResultSet calling next().

This is an example from java2s.com:

DatabaseMetaData md = conn.getMetaData();
ResultSet rs = md.getTables(null, null, "%", null);
while (rs.next()) {
  System.out.println(rs.getString(3));
}

Column 3 is the TABLE_NAME (see documentation of DatabaseMetaData::getTables).

edited Mar 21 '17 at 05:11

Basil Bourque

303,325
100
852
1,154

answered May 06 '10 at 10:38

Peter Lang

54,264
27
148
161

3

If this fails for you (like it did me) you need to make sure the database user can actually show tables on the database. – Kevin Parker Feb 13 '12 at 05:18
4

It shows all tables in all databases. You can pass the database name as the first parameter to shows tables only in that db. – Mahdi Jun 29 '16 at 08:52
2

For mysql, ResultSet rs = md.getTables(connection.getCatalog(), null, "%", new String [] {"TABLE"}); gives you only tables of your current schema. – krishnakumarp Jun 25 '19 at 06:10
Why are we giving a % here? Shouldn't we use something like ".*"? – Pardhu May 13 '20 at 21:07
1

@Pardhu: This is not a regex, `%` is used as wildcard in SQL. – Peter Lang May 14 '20 at 05:21

score 31 · Answer 2 · answered May 16 '13 at 06:28

31

 public void getDatabaseMetaData()
    {
        try {

            DatabaseMetaData dbmd = conn.getMetaData();
            String[] types = {"TABLE"};
            ResultSet rs = dbmd.getTables(null, null, "%", types);
            while (rs.next()) {
                System.out.println(rs.getString("TABLE_NAME"));
            }
        } 
            catch (SQLException e) {
            e.printStackTrace();
        }
    }

answered May 16 '13 at 06:28

Rohit

311
3
2

2

It shows all tables in all databases. You can pass the database name as the first parameter to shows tables only in that db. – Mahdi Jun 29 '16 at 08:52
Start with driver mysql-connector-java 8.0.22 metaData.getTables return all user visible tables from all databases, ignore parameter 'schema' – Yu Jiaao Feb 02 '21 at 16:06

score 8 · Answer 3 · answered Mar 21 '14 at 05:50

In your example problem is passed table name pattern in getTables function of DatabaseMetaData.

Some database supports Uppercase identifier, some support lower case identifiers. For example oracle fetches the table name in upper case, while postgreSQL fetch it in lower case.

DatabaseMetaDeta provides a method to determine how the database stores identifiers, can be mixed case, uppercase, lowercase see:http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#storesMixedCaseIdentifiers()

From below example, you can get all tables and view of providing table name pattern, if you want only tables then remove "VIEW" from TYPES array.

public class DBUtility {
    private static final String[] TYPES = {"TABLE", "VIEW"};
    public static void getTableMetadata(Connection jdbcConnection, String tableNamePattern, String schema, String catalog, boolean isQuoted) throws HibernateException {
            try {
                DatabaseMetaData meta = jdbcConnection.getMetaData();
                ResultSet rs = null;
                try {
                    if ( (isQuoted && meta.storesMixedCaseQuotedIdentifiers())) {
                        rs = meta.getTables(catalog, schema, tableNamePattern, TYPES);
                    } else if ( (isQuoted && meta.storesUpperCaseQuotedIdentifiers())
                        || (!isQuoted && meta.storesUpperCaseIdentifiers() )) {
                        rs = meta.getTables(
                                StringHelper.toUpperCase(catalog),
                                StringHelper.toUpperCase(schema),
                                StringHelper.toUpperCase(tableNamePattern),
                                TYPES
                            );
                    }
                    else if ( (isQuoted && meta.storesLowerCaseQuotedIdentifiers())
                            || (!isQuoted && meta.storesLowerCaseIdentifiers() )) {
                        rs = meta.getTables( 
                                StringHelper.toLowerCase( catalog ),
                                StringHelper.toLowerCase(schema), 
                                StringHelper.toLowerCase(tableNamePattern), 
                                TYPES 
                            );
                    }
                    else {
                        rs = meta.getTables(catalog, schema, tableNamePattern, TYPES);
                    }

                    while ( rs.next() ) {
                        String tableName = rs.getString("TABLE_NAME");
                        System.out.println("table = " + tableName);
                    }



                }
                finally {
                    if (rs!=null) rs.close();
                }
            }
            catch (SQLException sqlException) {
                // TODO 
                sqlException.printStackTrace();
            }

    }

    public static void main(String[] args) {
        Connection jdbcConnection;
        try {
            jdbcConnection = DriverManager.getConnection("", "", "");
            getTableMetadata(jdbcConnection, "tbl%", null, null, false);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

score 4 · Answer 4 · answered May 06 '10 at 19:59

4

If you want to use a high-level API, that hides a lot of the JDBC complexity around database schema metadata, take a look at this article: http://www.devx.com/Java/Article/32443/1954

answered May 06 '10 at 19:59

Sualeh Fatehi

4,700
2
24
28

score 4 · Answer 5 · edited Nov 09 '22 at 13:32

In newer versions of MySQL connectors the default tables are also listed if catalog is not passed

DatabaseMetaData dbMeta = con.getMetaData();
//con.getCatalog() returns database name
ResultSet rs = dbMeta.getTables(con.getCatalog(), "", null, new String[]{"TABLE"});
ArrayList<String> tables = new ArrayList<String>();
while(rs.next()) {
    String tableName = rs.getString("TABLE_NAME");
    tables.add(tableName);
}
return tables;

score 3 · Answer 6 · edited Nov 09 '22 at 13:33

public static ArrayList<String> getTablesList(Connection conn) throws SQLException {
    
    ArrayList<String> listofTable = new ArrayList<String>();
    
    DatabaseMetaData md = conn.getMetaData();
    
    ResultSet rs = md.getTables(null, null, "%", null);
    
    while (rs.next()) {
        if (rs.getString(4).equalsIgnoreCase("TABLE")) {
            listofTable.add(rs.getString(3));
        }
    }
    return listofTable;
}

score 0 · Answer 7 · answered Sep 29 '21 at 03:31

0

DatabaseMetaData md = conn.getMetaData();
String[] types = {"TABLE"};
ResultSet rs = md.getTables("Your_DB_Name", null, "%", types);
while (rs.next()) {
    System.out.println(rs.getString("TABLE_NAME"));
}

answered Sep 29 '21 at 03:31

virak seam

33
4

score -1 · Answer 8 · answered Jun 05 '19 at 12:26

@Transactional
@RequestMapping(value = { "/getDatabaseTables" }, method = RequestMethod.GET)
public @ResponseBody String getDatabaseTables() throws Exception{ 

    Connection con = ((SessionImpl) sessionFactory.getCurrentSession()).connection();
    DatabaseMetaData md = con.getMetaData();
    ResultSet rs = md.getTables(null, null, "%", null);
    HashMap<String,List<String>> databaseTables = new HashMap<String,List<String>>();
    List<String> tables = new ArrayList<String>();
    String db = "";
    while (rs.next()) {
        tables.add(rs.getString(3));
        db = rs.getString(1);
    }
    List<String> database = new ArrayList<String>();
    database.add(db);
    databaseTables.put("database", database);
    Collections.reverse(tables);
    databaseTables.put("tables", tables);
    return new ObjectMapper().writeValueAsString(databaseTables);
}

@Transactional
@RequestMapping(value = { "/getTableDetails" }, method = RequestMethod.GET)
public @ResponseBody String getTableDetails(@RequestParam(value="tablename")String tablename) throws Exception{ 
    System.out.println("...tablename......"+tablename);
    Connection con = ((SessionImpl) sessionFactory.getCurrentSession()).connection();       
     Statement st = con.createStatement();
     String sql = "select * from "+tablename;
     ResultSet rs = st.executeQuery(sql);
     ResultSetMetaData metaData = rs.getMetaData();
     int rowCount = metaData.getColumnCount();    
     List<HashMap<String,String>> databaseColumns = new ArrayList<HashMap<String,String>>();
     HashMap<String,String> columnDetails = new HashMap<String,String>();
     for (int i = 0; i < rowCount; i++) {
         columnDetails = new HashMap<String,String>();
         Method method = com.mysql.jdbc.ResultSetMetaData.class.getDeclaredMethod("getField", int.class);
         method.setAccessible(true);
         com.mysql.jdbc.Field field = (com.mysql.jdbc.Field) method.invoke(metaData, i+1);
         columnDetails.put("columnName", field.getName());//metaData.getColumnName(i + 1));
         columnDetails.put("columnType", metaData.getColumnTypeName(i + 1));
         columnDetails.put("columnSize", field.getLength()+"");//metaData.getColumnDisplaySize(i + 1)+"");
         columnDetails.put("columnColl", field.getCollation());
         columnDetails.put("columnNull", ((metaData.isNullable(i + 1)==0)?"NO":"YES"));
         if (field.isPrimaryKey()) {
             columnDetails.put("columnKEY", "PRI");
         } else if(field.isMultipleKey()) {
             columnDetails.put("columnKEY", "MUL");
         } else if(field.isUniqueKey()) {
             columnDetails.put("columnKEY", "UNI");
         } else {
             columnDetails.put("columnKEY", "");
         }
         columnDetails.put("columnAINC", (field.isAutoIncrement()?"AUTO_INC":""));
         databaseColumns.add(columnDetails);
     }
    HashMap<String,List<HashMap<String,String>>> tableColumns = new HashMap<String,List<HashMap<String,String>>>();
    Collections.reverse(databaseColumns);
    tableColumns.put("columns", databaseColumns);
    return new ObjectMapper().writeValueAsString(tableColumns);
}

How to get all table names from a database?

8 Answers8

Linked