How to get all table names from a database?
JavaDatabaseJdbcDatabase MetadataJava Problem Overview
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?
Java Solutions
Solution 1 - Java
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).
Solution 2 - Java
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();
}
}
Solution 3 - Java
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();
}
}
}
Solution 4 - Java
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
Solution 5 - Java
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;
}
Solution 6 - Java
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;
Solution 7 - Java
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"));
}
Solution 8 - Java
@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);
}