This week's book giveaway is in the Jobs Discussion forum.
We're giving away four copies of Java Interview Guide and have Anthony DePalma on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes Retrieve all table names from a DB for a user Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Retrieve all table names from a DB for a user" Watch "Retrieve all table names from a DB for a user" New topic

Retrieve all table names from a DB for a user

Aditya Suryam Adep

Joined: Nov 25, 2008
Posts: 2
Problem here is I can retrieve all the table names from DB using con.getMetadata.getTables(). This returns all the tables in the DB irrespective of wether user(used in connection string) has privileges or not. SO in next step when I am using one of the tables from the fetched result set, I get 'table or view does not exist'.

I tried to get privileges with con.getMetadata.getTablePrivileges(null,null,tablename). This narrows the tables further but still when I do a select in some of this table I face the same issue 'table or view does not exist'.

Am I doing something wrong in second step ?

Is there any other way of doing this in JDBC?

I need to get only those table names for which the user can perform select operation?

any help would be good.

Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3753

What database is this? Different databases handle metadata differently. You could perform the query inside of a try/catch block and assume a catch means the table is unaccessible, but I imagine there's a better route.

[OCA 8 Book] [Blog]
Aditya Suryam Adep

Joined: Nov 25, 2008
Posts: 2
Hi Scott,

Thank you for the reply.

I guess thats hard way of doing it.. :-) though I tried but the problem is not solved by that.

One more thing, I face in this is even though from getPrivileges() I filter out some tables for which SELECT is permitted (as they are retunred from teh method), still it says table or view does not exist. Infact it seems to be correct, I couldnt access those tables on DB. So what this method actually gives is still a mistery for me.

For eg : Follwoing is the method..
public static void printMetaData(){
ResultSet rs = con.getMetaData().getTables(null, null, "%",new String[]{"TABLE"});
ResultSet rs2;
String table;
table = rs.getString(3);
rs2 = con.getMetaData().getTablePrivileges(null, null, table);
System.out.println(table.substring(table.lastIndexOf('$')+1,table.length()) + " : "+rs2.getString("GRANTOR") + " : " + rs2.getString("GRANTEE") +" : " + rs2.getString("PRIVILEGE"));

}catch(Exception e){

This returns.. sample row :

Now this table POLICY_TAB is not accessbile by the user I connected with.

Currently I am trying on Oracle. But my requirement is independant of DB.

I agree. Here's the link:
subject: Retrieve all table names from a DB for a user
It's not a secret anymore!