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.
– Renatas M., Commented Sep 2, 2014 at 7:56
var reader = cmd.ExecuteReader(); if(reader.HasRows) Console.WriteLine("Exists") or if(reader.Read()) ... — Renatas M.
– Renatas M., Commented Sep 2, 2014 at 8:03
@Reniuz Update: Changed the query and works with your method. Thanks a lot. — john
– john, Commented Sep 2, 2014 at 8:09

BlitZ · Accepted Answer · 2014-09-02 08:00:27Z

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 2, 2014 at 8:00

answered Sep 2, 2014 at 7:53

BlitZ

12.2k3 gold badges52 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Kenan Zahirovic Over a year ago

Keep in mind: You need MySQL 5.0 to use the INFORMATION_SCHEMA

BlitZ Over a year ago

@KenanZahirovic OP is using Visual Studio 12 and MySQL workbench 6.1. I do really have a doubt, that OP has old database engine.

john Over a year ago

Thanks for the answer. Do you mind implementing this in the above C# code segment?

john Over a year ago

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 Demetriou Over a year ago

@john Then the accepted answer is not the answer, your comment is the answer

|

Collectives™ on Stack Overflow

Cleanest way to check if table exists - MySQL

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Linked

Related