Check if table exists in mysql

Question

Possible Duplicate:
MySQL - check if table exists without using “select from”

Can I rely on this query to find out if tables in the specified database exists or there may be some restrictions?

SELECT
    `information_schema`.`TABLES`.`TABLE_NAME`
FROM
    `information_schema`.`TABLES`
WHERE
    `information_schema`.`TABLES`.`TABLE_SCHEMA` = 'my_database_name'
AND `information_schema`.`TABLES`.`TABLE_NAME` IN (
    'table_name',
    'table_name',
    'table_name',
    'table_name',
    'table_name',
    'table_name'
)

P.S. I do not need to create a table, and just need to check whether it exists or not.

Answered already in: http://stackoverflow.com/questions/8829102/mysql-check-if-table-exists-without-using-select-from — Joe, Jan 02 '13 at 21:57
That should do the trick. Do you have some reason to doubt that it would work? — Eric Petroelje, Jan 02 '13 at 21:57

score 8 · Answer 1 · answered Jan 02 '13 at 22:00

8

Or you could use this (longer query).

SELECT count(*)
FROM information_schema.tables
WHERE table_schema = 'databasename'
AND table_name = 'tablename'

answered Jan 02 '13 at 22:00

Jordi Kroon

2,607
3
31
55

this is the better answer because it always returns something, either 0 or 1, rather than NULL – rocketman Jul 28 '22 at 01:16

bonCodigo · Answer 2 · 2013-01-02T22:04:26.757

3

You could do the following:

SHOW TABLES LIKE tablename;

As per OP's comment - Edit:

SQLFIDDLE DEMO

Check the demo out. It shows a code like this:

SHOW TABLES LIKE 'user%';

Results:

TABLES_IN_DB_2_8B528 (USER%)
user_images
users

edited Jan 02 '13 at 22:04

answered Jan 02 '13 at 21:58

bonCodigo

14,268
1
48
91

i have an array of tables... – Kin Jan 02 '13 at 22:00
@Kirix - you could just do a plain `SHOW TABLES` and search through the list for the ones you are looking for. A database shouldn't have *that* many tables that this would be a performance issue. – Eric Petroelje Jan 02 '13 at 22:02
@Kirix you may use the `wildcards` with `like` operator ;) it works, please take a look at the reference demo provided. – bonCodigo Jan 02 '13 at 22:05
@EricPetroelje is it possible in `SHOW TABLES` specify database? Be default i using other database. – Kin Jan 02 '13 at 22:05
1

@Kirix - yup, `SHOW TABLES IN database_name` should do it. – Eric Petroelje Jan 02 '13 at 22:06
Well if you are uncertain of the full name of tables, you could simply use multiple `like` `wildcards` with `or`. e.g. `SHOW TABLES FROM WHERE Tables_in_ LIKE '%user%'` `OR Tables_in_ LIKE '%img%';` [SQLfiddleDemo](http://sqlfiddle.com/#!2/8b528/13) – bonCodigo Jan 02 '13 at 22:10

Check if table exists in mysql

2 Answers2

Linked