Blog

MySQL 5.7 - Check if column Exist

I have go through more than 30 post in Stack Overflow, but all solution look similar and end up its not straight forward syntax to follow. You need procedure to execute it. So I write this post to help others developer to solved the problem to check if column exist in table in MySql

Yup, that a true story happened to me last month when I just want to check if column exist in MySql but end up can't find solution for this simple question. I believe many of you have done searching in google about this problem. try this keyword, change to another, put more details in query to google but all solution look same and when you try in your MySQL editor, it is show that the syntax is not valid.

Scenario

You want to check if column exist in table Orders. If exist, then do this else do that.  -- this is the scenario I want to achieve.

Solution

MySQL Syntax

 
SHOW COLUMNS FROM eComm.Orders LIKE 'BaseOrderGrandTotal'.
 

Code Behind - c#

In C# code I used this syntax like below.
 

 
DataSet dsColumns = new DataSet();
string checkColumn = string.Format("SHOW COLUMNS FROM {0}.Orders LIKE 'BaseOrderGrandTotal';", DBName);
MySqlDataAdapter ColumnsAvailable = new MySqlDataAdapter(checkColumn, dbConn);
ColumnsAvailable.Fill(dsColumns, "Columns");
if (DataHelper.DataSourceIsEmpty(dsColumns))
{
//data set empty, dont have new columns, alter table.
string alterStatement = String.Format(@"ALTER TABLE {0}.Orders                        
ADD COLUMN BaseOrderGrandTotal decimal(19,2) NULL", DBName);

dbComm = new MySqlCommand(alterStatement, dbConn);
rc = dbComm.ExecuteNonQuery();

}


Happy coding. B-)

 

 

ASP.NET, MySQL

 Back to listing