1

I have searched a lot but i could not find a query which will apply to any RDBMS to check whether a table exits. Some are ok with mysql and h2 but its not compatible with oracle. Any one has a solution for this.

This works for oracle but not h2 or mysql

select count(*) as tblCount from user_tables where table_name = 'ALERTS_HISTORY';
dnWick
  • 393
  • 3
  • 14

1 Answers1

3

The java.sql.DatabaseMetaData object (which is obtainable from a Connection via .getMetaData() has a getTables(…) function, which does what you want; the driver will care about the SQL.

llogiq
  • 13,815
  • 8
  • 40
  • 72
  • Thanks for the answer. I just check your solution with oracle db ..Following is the code. ResultSet rs=databaseMetaData.getTables(null,null,tableName,null); Boolean tableExist=false; if (rs.next()) { tableExist=true; } Table is exist in my schema but it won't go inside the if statement. Is there a mistake i have done? – dnWick Dec 08 '14 at 06:40
  • also when use with h2 with same parameters i'm getting e = {org.h2.jdbc.JdbcSQLException@9977}"org.h2.jdbc.JdbcSQLException: Column TYPE_NAME not found; I'm only sending the table name as a parameter. is there some more to send like catalog ,schema etc – dnWick Dec 08 '14 at 06:51
  • AFAIK, some oracle drivers do not like to allow table requests without catalog. Also which version of h2 are you using? The error may well be a bug in their JDBC implementation. – llogiq Dec 08 '14 at 06:58
  • 1
    @dnWick: don't forget that Oracle tables are stored in uppercase. So you have to supply the name in uppercase as well. `getTables()` *does* work with Oracle if you provide the parameters properly. You should also provide the schema (=user) name to avoid getting tables from other users with that call –  Dec 08 '14 at 07:08