Detect if table exists

[raffaele castagno]

Il Sun, 08 Aug 2004 23:46:11 +0530, Manish Hatwalne ha scritto:

This should be easy, but I am unable to find it now.
How can I detect if a particular table exists or not in my DB (I am using
MySQL).
I cannot delete table, so I can't use drop if exists; but can I use count
(*) if exists, or sth similar? I am not much of a DB person.
Any pointers would be very useful.

AFAIK, if you try to access a table wich does not exist, JDBS should raise
a DataAccessException, if I remember right... You can try to run a query
on the table you want to check, and catch that exception.

Read MySql JDBC driver and Java JDBC documentation for details, and of
course wait the answer of developer more experienced than me...

Bye

Raffaele

 

[Manish Hatwalne]

This should be easy, but I am unable to find it now.
How can I detect if a particular table exists or not in my DB (I am using
MySQL).
I cannot delete table, so I can't use drop if exists; but can I use count
(*) if exists, or sth similar? I am not much of a DB person.
Any pointers would be very useful.

TIA,
- Manish

 

[sks]

This should be easy, but I am unable to find it now.
How can I detect if a particular table exists or not in my DB (I am using
MySQL).
I cannot delete table, so I can't use drop if exists; but can I use count
(*) if exists, or sth similar? I am not much of a DB person.
Any pointers would be very useful.

You could use DESCRIBE tablename and catch the exception if it doesn't
exist.

Or you could use SHOW TABLES and loop through results to see if its exists.

 

[Tor Iver Wilhelmsen]

How can I detect if a particular table exists or not in my DB (I am using
MySQL).

Ask the driver for database metadata, and check for the table there.

Connection c = ...
DatabaseMetaData dbm = c.getMetaData();
ResultSet tables = dbm.getTableNames(null, null, "TABLENAME", null);
if (rs.next()) {
// Table exists
}
else {
// Table does not exist
}

Adjust parameters, check for multiple entries etc. as you see fit.

 

[Liz]

Ask the driver for database metadata, and check for the table there.

Connection c = ...
DatabaseMetaData dbm = c.getMetaData();
ResultSet tables = dbm.getTableNames(null, null, "TABLENAME", null);
if (rs.next()) {
// Table exists
}
else {
// Table does not exist
}

Adjust parameters, check for multiple entries etc. as you see fit.

I use
execute("SHOW TABLES");
which returns a result set that contains the names of all tables in
the database.
This way you have no exception to worry about.