How can I check if a column exists into a table from a database? [duplicate]

Question

I have a huge database and a column name that I am not sure if it exists in any of my tables. How can I check its existance?

Answer 1

SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'db_name'
AND COLUMN_NAME = 'column_name'

Answer 2

If you need to check all tables, you may have to list the tables first:

SHOW TABLES FROM database_name;

Then, loop through the tables (in code, e.g., PHP), and execute this query:

SHOW COLUMNS FROM database_name.table_name LIKE 'mycol';

This will return a result if the column exists on the table.

You can also select directly from the information_schema, but I have found this to be very slow in some cases with MySQL and large databases.