Now I am using something like:
dbResult = dbStatement.executeQuery("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '[e2]' AND table_name = '[emp101_messages]'");
while(dbResult.next())
{
int value = -1;
value= dbResult.getInt(1);
System.out.println(value + " table count");
}
Is this correct way to check if a table exists in a database?
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'dbname'
AND table_name = 'my_tablename';
+----------------------+
| table_name |
+----------------------+
| my_tablename |
+----------------------+
As most of the options are already provided. Please see if this can help.
Use this:
SHOW [FULL] TABLES [{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
As sometimes you don't have access to metadata.
If you want just check if 1, 2 or a few more tables exists why not use: mysql> show tables like "test1";?
It is a correct way to do so. (Just puzzled by the "[]" braces around your table names -- these are most probably not part of the actual names, so need to be removed)
Moreover, it is an efficient way: since you are providing a constant for both table_schema as well as for table_name, you are therefore utilizing INFORMATION_SCHEMA optimizations, in that the table is not even opened:
explain select count(*) from information_schema.tables where table_schema='world' and table_name='City';
+----+-------------+--------+------+---------------+-------------------------+---------+------+------+---------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+-------------------------+---------+------+------+---------------------------------------------------+
| 1 | SIMPLE | tables | ALL | NULL | TABLE_SCHEMA,TABLE_NAME | NULL | NULL | NULL | Using where; Skip_open_table; Scanned 0 databases |
+----+-------------+--------+------+---------------+-------------------------+---------+------+------+---------------------------------------------------+
The "SHOW TABLES" solution offered is good, too -- and practically the same as far as Java/Python/Whatever code goes. The result is a valid ResultSet:
SHOW TABLES FROM world LIKE 'City';
+------------------------+
| Tables_in_world (City) |
+------------------------+
| City |
+------------------------+
But to just complete the story: it is not a standard SQL syntax - so if you're using some framework like an ORM of some sorts, you may not always be able to get by with this type of query (as I recall EJB3 will not let you do so).
Also, it is very difficult to parse on server side, see: Reading results of SHOW statements, on server side, though this may not be a concern to you.
You can also use:
SHOW TABLES LIKE "emp101_messages";
I think it's more efficient.
