Shafizadeh Shafizadeh - 6 months ago 22
SQL Question

How do I check whether column exists in the table?

I need to check whether

mytable
table is containing
mycolumn
column? Here is my query:

SELECT CASE WHEN EXISTS (SHOW COLUMNS FROM mytable LIKE mycolumn) THEN 1 ELSE 0 END;


But it doesn't work and throws this error-message:


#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
near 'SHOW COLUMNS FROM mytable LIKE mycolumn) THEN 1 ELSE 0 END at line 1


What's wrong and how can I fix it?

Answer

You can use the following as an if

IF EXISTS(
    select * from 
    INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME ='SOMETABLE' AND
    COLUMN_NAME = 'SOMECOLUMN')
)   
BEGIN
     -- do stuff
END
GO

Alternatively as a case

SELECT CASE WHEN EXISTS(
    select * from 
    INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME ='TABLE_NAME' AND
    COLUMN_NAME = 'COLUMN_NAME') 
Then 1 Else 0 End;