CODEWITHSUNDEEP
javaderby
Eric
Eric

Reputation: 373

Java DB Database, check for all table names

I've got an embedded Derby Database in my java application, and have multiple table's (that are created and deleted, so nothing is set in stone). I wanted to be able to return a list of names of all the tables currently in the database as I have to display the list in the application as well as get all the information from them.

Easiest way to do so? I don't need code just a method or methods. I'm a terrible google-fu user.

Currently my code works by grabbing a ResultSet from a specific table name entered, but it's only for testing purposes and I need to be able to display the full list of tables I have.

EDIT: My current workaround is actually different than posted. I simply have another table that holds all the table names created and updates when one is created/deleted. Obviously not the best approach but it works for me right now.

Upvotes: 1

Views: 2383

Answers (3)

Lee Chu
Lee Chu

Reputation: 41

Try this:

select tableName from sys.systables

You should get all the tables your system.

Upvotes: 0

Dyre
Dyre

Reputation: 547

Using metadata is the (somewhat) more portable solution. Note that you don't need the catalog stuff with Derby, as there are no catalogs. You can issue dmd.getTables(...) directly with null for the catalog. If all the tables you track are in a single schema, (and there aren't any other tables in that schema), getTables(null, "schemaName", null, null) should do the trick.

If need more fancy querying and you're not concerned about portability, you can check out

the dataBaseMetaData tool which gives you access to metadata as tables so that you can perform joins and other sophisticated queries on them.

Upvotes: 0

durgas
durgas

Reputation: 86

DatabaseMetaData metaData = connection.getMetaData();
ResultSet resultSet = metaData.getTables(null, "schenaName", "%" ,new String[] {"TABLE"} );
while (resultSet.next()) {
  System.out.println(resultSet.getString(3));
}

Adding new answer:

 Connection connection = getDBConnection();
 DatabaseMetaData dbMetaData = connection.getMetaData();
 //getting catalogs for mysql DB, if it is not working for your DB, try dbMetaData.getSchemas();
 ResultSet catalogs = dbMetaData.getCatalogs();
 while(catalogs.next()){
       String catalogName = catalogs.getString(1);
         //excluding table names from "mysql" schema from mysql DB.
       if(!"mysql".equalsIgnoreCase(catalogName)){
            ResultSet tables = dbMetaData.getTables(catalogName, null, null, null);
            while(tables.next()){
                 System.out.println(catalogName + "::"+tables.getString(3));
            }
        }
  }

Upvotes: 2

Related Questions