CODEWITHSUNDEEP
javajdbc
Jjreina
Jjreina

Reputation: 2713

How can I get all tables names of a schema?

How can I get all tables names of a schema? I have tried this:

DatabaseMetaData metaData = (DatabaseMetaData) conn.getMetaData();
metaData.getTables(null, schema, null, null);

but it does not work.

Finally I have made this:

conn.setCatalog(mySchema);
String sqlQuery = "show tables";
rs = ps.executeQuery(sqlQuery); 
while (rs.next())
{
   System.out.print(rs.getString(1));
}

Upvotes: 3

Views: 2346

Answers (3)

nantitv
nantitv

Reputation: 3733

For mysql:5.6 -

To get particular tables of a particular schema, I have to pass the schema name as "catalog" parameter(1st param). It was ignoring, if I pass it as "schemaPattern" parameter( 2nd param).

I assumed that it has to do with the fact that "create schema' is a synonym for "create database" in mysql. https://dev.mysql.com/doc/refman/8.0/en/create-database.html

DatabaseMetaData meta = (DatabaseMetaData) 

support.getConnection().getMetaData();
    rs = meta.getTables("schema_name", null, null, new String[] { "TABLE" });
    while (rs.next()) {
        String tblName = rs.getString("TABLE_NAME");
        System.out.println(tblName);
    }

Upvotes: 2

luke657
luke657

Reputation: 835

ResultSet tables = metaData.getTables(null, null, null, new String[] {"TABLE"});
while (tables.next()){
    System.out.print(tables.getString("TABLE_NAME") + " ");
}

EDIT: Second parameter is where you specify the schema pattern (e.g. "Sales"), null for all schemas.

Upvotes: 5

Konstantin Yovkov
Konstantin Yovkov

Reputation: 62854

Try the following:

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

The documentation says that the third column is TABLE_NAME.

Upvotes: 4

Related Questions