How to query whether a table exists?

[sandstones]

I found a post at http://h2-database.66688.n3.nabble.com/Best-practice-Test-for-existence-of-table-etc-td4024451.html

 which said that the correct method was:   select count(*) from information_schema.tables where table_name = ?

However, I cannot get any results in doing such a search if I have a where clause in the statement.

I have been testing this from the H2 web console and driving my self nuts

If I use "SELECT * FROM INFORMATION_SCHEMA.TABLES"  I get all the tables
If I use any kind of where clause on any of the columns I get zero rows. For example

where table_name='name'
where table_name = null
where table_name ! =null
where table_name like 'name'
where table_type != null

Is there any way to modify the query to get this to work?

[Ryan How]

works for me.

you want IS NOT NULL, IS NULL instead of = null

This is what I did

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'FILE_RESOURCE'

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'FILE_RESOURCE';

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	STORAGE_TYPE	SQL	REMARKS	LAST_MODIFICATION	ID	TYPE_NAME	TABLE_CLASS	ROW_COUNT_ESTIMATE
GLHBUILDFLOW	PUBLIC	FILE_RESOURCE	TABLE	CACHED	CREATE CACHED TABLE PUBLIC.FILE_RESOURCE(     FILE_RESOURCE_ID LONG DEFAULT (NEXT VALUE FOR PUBLIC.SYSTEM_SEQUENCE_23C5B2D5_89BA_406C_935E_9CC776D79E73) NOT NULL NULL_TO_DEFAULT SEQUENCE PUBLIC.SYSTEM_SEQUENCE_23C5B2D5_89BA_406C_935E_9CC776D79E73 SELECTIVITY 100,     FILE_NAME VARCHAR_IGNORECASE(255) NOT NULL SELECTIVITY 66,     PARENT_FILE_RESOURCE_ID LONG SELECTIVITY 55,     FILE_RESOURCE_ROOT_ID LONG SELECTIVITY 11,     DIRECTORY BOOLEAN NOT NULL SELECTIVITY 22,     DATE_MODIFIED TIMESTAMP SELECTIVITY 77,     FILE_LENGTH LONG SELECTIVITY 88,     MD5_SUM VARCHAR_IGNORECASE(255) SELECTIVITY 11 )		0	109	null	org.h2.table.RegularTable	10

(1 row, 1 ms)

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.