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  lt  tables getMetaData   getColumnCount    i         System out println  table       tables getMetaData   getTableName i        Could someone help me on this

User · Answer

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 lt String List lt String gt  gt  databaseTables   new HashMap lt String List lt String gt  gt         List lt String gt  tables   new ArrayList lt String gt         String db           while  rs next              tables add rs getString 3            db   rs getString 1             List lt String gt  database   new ArrayList lt String gt         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 lt HashMap lt String String gt  gt  databaseColumns   new ArrayList lt HashMap lt String String gt  gt          HashMap lt String String gt  columnDetails   new HashMap lt String String gt          for  int i   0  i  lt  rowCount  i               columnDetails   new HashMap lt String String gt              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 lt String List lt HashMap lt String String gt  gt  gt  tableColumns   new HashMap lt String List lt HashMap lt String String gt  gt  gt         Collections reverse databaseColumns       tableColumns put  columns   databaseColumns       return new ObjectMapper   writeValueAsString tableColumns

User · Answer

public static ArrayList lt String gt  getTablesList Connection conn              throws SQLException            ArrayList lt String gt  listofTable   new ArrayList lt String gt              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

User · Answer

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 lt String gt  tables   new ArrayList lt String gt             while rs next                 String tableName   rs getString  TABLE NAME                tables add tableName                     return tables

User · Answer

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

User · Answer

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  amp  amp  meta storesMixedCaseQuotedIdentifiers                               rs   meta getTables catalog  schema  tableNamePattern  TYPES                         else if    isQuoted  amp  amp  meta storesUpperCaseQuotedIdentifiers                                 isQuoted  amp  amp  meta storesUpperCaseIdentifiers                                rs   meta getTables                                  StringHelper toUpperCase catalog                                   StringHelper toUpperCase schema                                   StringHelper toUpperCase tableNamePattern                                   TYPES                                                                          else if    isQuoted  amp  amp  meta storesLowerCaseQuotedIdentifiers                                     isQuoted  amp  amp  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

User · Answer

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

User · Answer

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

[java] How to get all table names from a database?

Examples related to java

Examples related to database

Examples related to jdbc

Examples related to database-metadata