H2: how to tell if table exists?

Question

I'm trying to write Java code that checks to see if an H2 table exists: if it doesn't exist, it first executes a CREATE TABLE query that creates the table before it proceeds any further.

I followed the advice in this Google Groups question but it simply does not work.

If I run the following query:

SELECT COUNT(*) AS count FROM information_schema.tables WHERE table_name = 'word_types'

I get back a single row with a COUNT field which has a value of 0; this indicates that the word_types tables doesn't exist. But when I run:

SELECT * FROM word_types

I get back 0 result sets, but the SQL frontend/GUI that I'm using shows me all the fields/columns that exist in the word_types table. Additionally, when I drill down into my database's list of available tables (using the same GUI), I see word_types exists.

So what is the correct query to use when trying to determine if an H2 table exists or not? Using v1.3.173. Thanks in advance!

It might be an uppercase/lowercase problem when querying the system table. Try it again with the the lower function. — Dan Bracuk, Oct 22 '13 at 12:55
Why not to use `CREATE TABLE IF NOT EXIST myTable`? What is your logic, you check it every time before doing a query or just once when application starts? — Admit, Oct 22 '13 at 13:04
Well your approach runs fine for me - if first query returns 0, than second one gives error code `Table doesn't exist` — Antoniossss, Oct 22 '13 at 13:10
Thanks @DanBracuk (+1) - where should I apply the lower function? Can you provide a code example? Thanks again! — , Oct 22 '13 at 13:38
Thanks @Admit (+1) - not a bad suggestion and if all else fails here I might go with it. My logic is to check once at app startup. — , Oct 22 '13 at 13:38
Than it's definitely a way to go. Since you will do just one DB call and if table exist, nothing will happen. — Admit, Oct 22 '13 at 13:41

score 78 · Accepted Answer · answered Oct 22 '13 at 13:59

78

First: check the case in which you type tables' names. It's very important. word_types and WORD_TYPES are two different tables.
Second: If you want to check if table exists and if it doesn't then create one, I recommend you to use the following example:

CREATE TABLE IF NOT EXISTS TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));

answered Oct 22 '13 at 13:59

qiGuar

1,726
1
18
34

You nailed it @qiGuar (+1) - it was a casing issue. When I created the table, I specified the table name as `word_types`. And when I run `SELECT * FROM word_types` I see the table exists but no rows come back (which is correct, I haven't inserted anything into it yet). But I have to specify the table name as `WORD_TYPES` in the query that checks the `information_schema.tables` table for the existing of my `word_types` table. Weird. – Oct 22 '13 at 14:09
It's because the table name is stored in upper case in `information_schema.tables` and commands are case sensitive. But I can't tell you why names are stored this way. – qiGuar Oct 22 '13 at 14:15
Unquoted tablenames are usually case insensitive, but they are in most database stored always in the same case (usually uppercase, sometimes lowercase). – Mark Rotteveel Oct 22 '13 at 14:59
See my answer. There is a configuration setting `DATABASE_TO_UPPER` which is responsible for this behavior, because it is set to `TRUE` per default. – Stefan Winkler Nov 25 '13 at 15:52

score 19 · Answer 2 · edited May 03 '17 at 18:50

There is also a JDBC API which you can use to query the existence of one or more tables. This is (in theory) more portable than a direct query which uses information_schema.

(In practice, the portability is still somewhat limited by the fact that different DBMS define and use the concepts schema and catalog slightly differently).

This is how it works:

boolean tableExists = false;

Connection conn = getConnection(); // get a DB connection from somewhere
ResultSet rset = conn.getMetaData().getTables(null, null, "WORD_TYPES", null);
if (rset.next())
{
  tableExists = true;
}

Instead of "WORD_TYPES" you can also use SQL-Style wildcards, e.g. "WORD_%".

Note that H2 has a configuration setting DATABASE_TO_UPPER which is set to true per default. So any table name is converted to upper case which is why you need to query for the table in upper case (or set DATABASE_TO_UPPER to false).

Also, using the other parameters (which I have set to null here), you can further restrict the search scope to a specific scema or table type.

The resultset also contains meta-information about the table, if you need that, e.g., the schema or table comment.

See the JavaDoc for a complete list of options and available metadata.

ResultSet rset = conn.getMetaData().getTables(null, null, "WORD_TYPES", null)) is always returning null for me.... — Anupam Maiti, Aug 28 '14 at 06:45

score 0 · Answer 3 · answered Oct 22 '13 at 12:50

0

If the second query doesn't throw an exception or return any rows, it just means the table exists but is empty.

answered Oct 22 '13 at 12:50

Kayaman

72,141
5
83
121

score 0 · Answer 4 · answered Oct 22 '13 at 13:00

0

Your above query will return the records count not existence of your table, for that you have to fire the follwing query. This query will return you all table of your Database and from there you can check your "word_types" exists or not.

USE YOURDBNAME GO SELECT * FROM sys.Tables GO

reply me if it work or not works also

answered Oct 22 '13 at 13:00

Rohit Goyla

1
2

Please use code sample button on editor to mark up sql sentences, code examples, etc. You can edit your answer to use the button, it helps writing more readable answers. – Kuzgun Oct 22 '13 at 13:20
Thanks @Rohit Goyla (+1) - but I get the following error when I try your SQL: `Error: Syntax error in SQL statement "USE[*] MYAPP_DB GO SELECT * FROM SYS.TABLES GO "; expected "UPDATE, {"; SQL statement: USE myapp_db GO SELECT * FROM sys.Tables GO [42001-173] SQLState: 42001 ErrorCode: 42001` – Oct 22 '13 at 13:37
1

The statement `USE YOURDBNAME` is for Microsoft SQL Server as far as I know. The question is for the [H2 database](http://h2database.com) however. – Thomas Mueller Oct 22 '13 at 17:59

score 0 · Answer 5 · answered Jan 28 '19 at 19:48

    Connection con = getConnection();
    Boolean tableExist = false;
    PreparedStatement preparedStatement = con.prepareStatement("SHOW TABLES FROM INFORMATION_SCHEMA");
        Boolean rq = preparedStatement.execute();
        if(rq) {
                ResultSet rs = preparedStatement.getResultSet();
                while (rs.next()) {
                    if(rs.getString(rs.getRow()).equals("WORD_TYPES")) {
                        isExist = true;
                        break;
                }
            }
        }

H2: how to tell if table exists?

5 Answers5