Cleanest way to check if table exists - MySQL

Question

Working with C# and MySQL here (Visual Studio 12 and MySQL workbench 6.1).

This is what I have so far.

string strCheck = "SHOW TABLES LIKE \'emp\'";
MySqlCommand cmd = new MySqlCommand(strCheck, con);
cmd.Prepare();

if (cmd.ExecuteNonQuery() > 0)
{
     Console.WriteLine("exists");
}
else
{
     Console.WriteLine("does not");
}

I have seen many questions here (mostly related to PHP) but they don't seem to be working for me. Also, I don't want a solution where we check if the table has any rows, because the table can be empty, and what I want to know is whether it exists.

Thanks.

`ExecuteNonQuery` will return number of rows affected and I think you not affecting any rows in your SQL statement. Try to use `Reader`. — Renatas M., Sep 02 '14 at 07:56
`var reader = cmd.ExecuteReader(); if(reader.HasRows) Console.WriteLine("Exists")` or `if(reader.Read()) ...` — Renatas M., Sep 02 '14 at 08:03
@Reniuz Update: Changed the query and works with your method. Thanks a lot. — john, Sep 02 '14 at 08:09

BlitZ · Accepted Answer · 2014-09-02T08:00:27.837

3

Try the following SELECT statement:

SELECT EXISTS(
    SELECT
        `TABLE_NAME`
    FROM
        `INFORMATION_SCHEMA`.`TABLES`
    WHERE
        (`TABLE_NAME` = 'emp')
        AND
        (`TABLE_SCHEMA` = 'mydb')
) as `is-exists`;

edited Sep 02 '14 at 08:00

answered Sep 02 '14 at 07:53

BlitZ

12,038
3
49
68

Keep in mind: You need MySQL 5.0 to use the INFORMATION_SCHEMA – Kenan Zahirovic Sep 02 '14 at 07:54
@KenanZahirovic OP is using `Visual Studio 12 and MySQL workbench 6.1`. I do really have a doubt, that OP has old database engine. – BlitZ Sep 02 '14 at 07:58
Thanks for the answer. Do you mind implementing this in the above C# code segment? – john Sep 02 '14 at 08:06
@SuperJohn Well, I do not actually strong in `C#`. I suggest you to follow this [link](http://stackoverflow.com/questions/9787097/mysql-select-where-and-c-sharp). – BlitZ Sep 02 '14 at 08:09
3

I solved the problem. My previous query works but I had to use `var reader = cmd.ExecuteReader(); if(reader.HasRows) Console.WriteLine("Exists")`. Thanks anyways! – john Sep 02 '14 at 08:10
1

@john Then the accepted answer is not the answer, your comment is the answer – John Demetriou Sep 26 '19 at 16:11

Cleanest way to check if table exists - MySQL

1 Answers1