Show tables in database
-------------- ShowTables.java -----------------
import java.io.*;
import java.sql.*;
import java.util.Properties;
public class ShowTables {
Writer out;
Connection conn;
Statement stat;
String driver, url, username, password, outfile, pattern;
String[] arrTables;
int limit = 0;
ShowTables(String argv[]) {
File file;
if (argv.length > 0 && !argv[0].isEmpty()) {
file = new File(argv[0]);
if (!file.isFile()) throw new RuntimeException(file.getName() + " file not found");
} else {
file = new File("config.properties");
if (!file.exists()) createExample(file);
}
try (InputStream input = new FileInputStream(file)) {
Properties prop = new Properties();
if (file.getName().endsWith(".xml")) prop.loadFromXML(input);
else prop.load(input);
driver = prop.getProperty("driver", "").trim();
url = prop.getProperty("url", "").trim();
username = prop.getProperty("username", "").trim();
password = prop.getProperty("password", "").trim();
outfile = prop.getProperty("outfile", "").trim();
if (outfile.isEmpty()) outfile = "out.txt";
String types = prop.getProperty("types", "").trim();
arrTables = (types.isEmpty() ? null : types.split("\\s*(,|;)\\s*"));
pattern = prop.getProperty("pattern", "").trim();
if (pattern.isEmpty()) pattern = null;
try { limit = Integer.parseInt(prop.getProperty("limit")); } catch (Exception e) { }
} catch (Exception e) { e.printStackTrace(); }
try {
out = new FileWriter(outfile);
println("Driver: " + driver);
println("Url: " + url);
println("Name: " + username);
println("Password: " + password);
if (arrTables != null && arrTables.length > 0) {
StringBuilder sb = new StringBuilder();
for (int i = 0; i < arrTables.length; i++) sb.append(i == 0 ? "Types: " : ", ").append(arrTables[i]);
println(sb.toString());
}
if (pattern != null) println("Pattern: " + pattern);
Driver d = (Driver) Class.forName(driver).newInstance();
DriverManager.registerDriver(d);
conn = DriverManager.getConnection(url, username, password);
stat = conn.createStatement();
show();
} catch (Exception e) {
e.printStackTrace();
} finally {
try { stat.close(); } catch (Exception e) { }
try { conn.close(); } catch (Exception e) { }
try { out.close(); } catch (Exception e) { }
}
}
void show() throws SQLException, IOException {
DatabaseMetaData dbmd = conn.getMetaData();
try (ResultSet rst = dbmd.getTables(null, null, pattern, arrTables)) {
while (rst.next()) {
String tableType = rst.getString("TABLE_TYPE");
String tableSchema = rst.getString("TABLE_SCHEM");
String tableName = rst.getString("TABLE_NAME");
String table = (tableSchema.isEmpty() ? tableName : tableSchema + "." + tableName);
println(tableType + " " + table);
try (ResultSet rsr = stat.executeQuery("SELECT * FROM " + table)) {
ResultSetMetaData md = rsr.getMetaData();
int columns = md.getColumnCount();
int[] size = new int[columns];
String sl = "+", sn = ":";
for (int i = 1; i <= columns; i++) {
println(" " + md.getColumnName(i) + " " +
md.getColumnTypeName(i) + "(" +
md.getPrecision(i) + "." + md.getScale(i) + ")" +
(md.isNullable(i) == 0 ? " NotNull" : "") +
(md.isAutoIncrement(i) ? " Autoincrement" : "" ));
int dsize = md.getColumnDisplaySize(i);
String columnName = md.getColumnName(i);
if (dsize < 1 || dsize > 100) dsize = 20;
if (columnName.length() > dsize) dsize = columnName.length();
sl += repeat("-", dsize + 2) + "+";
sn += " " + columnName + repeat(" ", dsize - columnName.length()) + " :";
size[i - 1] = dsize;
}
println(sl);
println(sn);
println(sl);
StringBuilder sb = new StringBuilder();
int count = 0;
while (rsr.next()) {
sb.setLength(0);
sb.append(":");
for (int i = 1; i <= columns; i++) {
sb.append(" ");
Object o = rsr.getObject(i);
String v = (o == null ? "" : o.toString());
for (int j = 0, dsize = size[i - 1]; j < dsize; j++) {
if (j < v.length()) {
char ch = v.charAt(j);
if (ch > 31) sb.append(ch);
else sb.append('?');
} else sb.append(' ');
}
sb.append(v.length() > size[i - 1] ? ">:" : " :");
}
println(sb.toString());
count++;
if (limit > 0 && count >= limit) break;
}
println(sl);
if (rsr.next()) {
println("Limit: " + count);
count = -1;
try (ResultSet rsc = stat.executeQuery("SELECT COUNT(*) FROM " + table)) {
if (rsc.next()) count = rsc.getInt(1);
} catch (Exception e) { }
}
if (count >= 0) println("Row count: " + count);
}
try (ResultSet rsi = dbmd.getIndexInfo(null, tableSchema, tableName, false, false)) {
if (rsi.next()) {
println("Indexes:");
boolean nonUnique = rsi.getBoolean("NON_UNIQUE");
String indexName = rsi.getString("INDEX_NAME");
String s = "(" + rsi.getString("COLUMN_NAME");
while (rsi.next()) {
if (indexName.equals(rsi.getString("INDEX_NAME"))) {
s += ", " + rsi.getString("COLUMN_NAME");
} else {
printInd(indexName, s, nonUnique);
nonUnique = rsi.getBoolean("NON_UNIQUE");
indexName = rsi.getString("INDEX_NAME");
s = "(" + rsi.getString("COLUMN_NAME");
}
}
printInd(indexName, s, nonUnique);
}
}
}
}
}
void printInd(String indexName, String s, boolean nonUnique) throws IOException {
println(" " + indexName + " " + s + (nonUnique ? ")" :") Unique"));
}
String repeat(String s, int n) {
StringBuilder sb = new StringBuilder(s.length() * n);
for(int i = 0; i < n; i++) sb.append(s);
return sb.toString();
}
void println(String s) throws IOException {
out.append(s).append("\n");
System.out.println(s);
}
void createExample(File file) {
try (Writer wr = new FileWriter(file)) {
wr.write("driver=org.apache.derby.jdbc.EmbeddedDriver\nurl=jdbc:derby:DB;create=true\n");
wr.write("name=\npassword=\noutfile=\ntypes=\npattern=\nlimit=\n");
} catch (Exception e) { }
}
public static void main(String argv[]) { new ShowTables(argv); }
}