Get list of existing table names
Thotheolh
names for a software I am writing so that it can automatically which
database tables are not installed so it can be installed
automatically. I am looking into allowing my software to automate
installation and setting up tables in h2 databases.
I tried to use SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_SCHEMA='PUBLIC' but I noticed that if I do a 'DROP ALL OBJECTS
DELETE FILES' and revisit the same database again, the information
schema would still contain the names of the old tables even though the
tables are already dropped.
Is there any command to quickly get a reliable list of existing table
names which I can execute from a PreparedStatement in Java ?
Sam Van Oort
?
The information schema should work for all DDL statements, and if it
doesn't for DROP ALL OBJECTS... then this is probably a bug. Can you
report which version you are using and if this holds for the most
recent one?
Also: DROP ALL OBJECTS DELETE FILES is sort of a special nuke-
everything statement, so it is understandable if it doesn't work quite
right.
Regards,
Sam Van Oort
Thomas Mueller
> How do I get a list of table names ?
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES...
> but I noticed that if I do a 'DROP ALL OBJECTS
> DELETE FILES' and revisit the same database again, the information
> schema would still contain the names of the old tables even though the
> tables are already dropped.
Well, that's not good. Are you sure? I can't reproduce this. Could you
post a simple, standalone test case that reproduces the problem? It
would be great if the test case does not have any dependencies except
the H2 jar file (that is, a simple SQL script that can be run in the
H2 Console, or a Java class uses the JDBC API and is run using a
static main method). Please include any initialization code (CREATE
TABLE, INSERT and so on) in the Java class or in a .sql script file.
Regards,
Thomas