MySQL - Does a Table have a Specific Column Name

Question

I can find numerous search results explaining how to search the database or information schema for a specific column name (example).

Is there a query that will tell me whether a specific column name is in a table, where I already know the table I want to search?

The question is more specific than "Give me all column names of a table". It's more like, "Does this column exist in this table, yes or no?"

Ex: IS COLUMN as_id IN TABLE assets

If you know how to search `INFORMATION_SCHEMA.columns` to get all the column names, it should be obvious how to put that question into the `WHERE` clause of a query. — Barmar, Feb 16 '16 at 22:41
What stops you from adding one more `AND` into `WHERE` clause? — PM 77-1, Feb 16 '16 at 22:42
The second answer in the question you linked to even shows how to test the column name. — Barmar, Feb 16 '16 at 22:43
Ah, I see then, thank you. I thought there might be a more direct query, but I guess this one will do: `SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='my_database' AND `TABLE_NAME`='$table'` — TARKUS, Feb 16 '16 at 22:47
`SELECT my_col_name FROM my_table LIMIT 1;` - if you don't get an error, then the column exists :-) — Paul Spiegel, Feb 16 '16 at 22:52

score 1 · Accepted Answer · edited May 23 '17 at 11:50

1

This seemed to work:

SELECT COUNT(*)
FROM information_schema.columns
WHERE table_schema='{database name}'
AND table_name='{table name}' AND COLUMN_NAME='{column name}'

edited May 23 '17 at 11:50

Community

answered Feb 16 '16 at 22:46

Jason Savell

1 Answers1