How do I check whether column exists in the table?

Question

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?

It is strange you think you need to check this with an SQL query. You should *know* what columns are in your tables. — Thorsten Kettner, May 18 '16 at 10:54
@ThorstenKettner Yes that's kinda odd ..! But actually I'm creating a dynamic query and sometimes I need to check `x` column exists or not. — Shafizadeh, May 18 '16 at 10:56

score 4 · Accepted Answer · edited May 18 '16 at 10:51

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;

score 3 · Answer 2 · edited May 18 '16 at 12:08

3

Try this instead

SELECT CASE WHEN EXISTS (
  SELECT * FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = 'db_name' 
    AND TABLE_NAME = 'table_name' 
    AND COLUMN_NAME = 'column_name') 
then 1 
else 0 
end;

edited May 18 '16 at 12:08

Piyush Gupta

2,181
3
13
28

answered May 18 '16 at 10:44

Seda

243
1
9

score 1 · Answer 3 · answered May 18 '16 at 11:23

1

If you need to pass the Table and Column name dynamically , please use this.

DECLARE @Table Varchar(100)
DECLARE @Column Varchar(100)
DECLARE @Query nvarchar(max)

SET @Table ='MyTable'
SET @Column ='MyColumn'


SET @Query ='select * from 
    INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME ='''+@Table+''' AND
    COLUMN_NAME IN ('''+@Column+''')'

EXEC (@Query)

answered May 18 '16 at 11:23

Praveen ND

540
2
10

I really don't want to do that `:-)` .. Thx anyway +1 – Shafizadeh May 18 '16 at 11:29

How do I check whether column exists in the table?

3 Answers3