How to query whether a table exists?

949 views
Skip to first unread message

sandstones

unread,
Aug 22, 2013, 1:44:19 AM8/22/13
to h2-da...@googlegroups.com
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

unread,
Aug 22, 2013, 1:52:58 AM8/22/13
to h2-da...@googlegroups.com
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.

Reply all
Reply to author
Forward
0 new messages