CODEWITHSUNDEEP
mysqlsql
Shafizadeh
Shafizadeh

Reputation: 10360

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?

Upvotes: 0

Views: 153

Answers (3)

Seda
Seda

Reputation: 243

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;

Upvotes: 3

Praveen ND
Praveen ND

Reputation: 560

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)

Upvotes: 1

Chris
Chris

Reputation: 925

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;

Upvotes: 4

Related Questions