How to query multiple tables to see if they share a common column?

Question

Say I have 100 tables without a uniform naming system. I.e. table 1 is named "tbl_Budget", table 2 is "tbl_Price", etc. I need an efficient way to query each of them to determine if they have a particular column name, say column A, then need the output to look something like this:

TABLE NAME | HAS COLUMN
tbl_Budget   TRUE
tbl_Price    FALSE

In other words, I need to do something like this, but for multiple tables: https://stackoverflow.com/questions/3395798/mysql-check-if-a-column-exists-in-a-table-with-sql

Answer 1

One possible way is selecting from information_schema.tables and a correlated subquery from information_schema.columns.

SELECT t.table_name `TABLE _NAME`,
       coalesce((SELECT 'TRUE'
                        FROM information_schema.columns c
                        WHERE c.table_schema = t.table_schema
                              AND c.table_name = t.table_name
                              AND c.column_name = '<column>'),
                'FALSE') `HAS COLUMN`
       FROM information_schema.tables t;

And another is conditional aggregation from information_schema.columns.

SELECT table_name `TABLE NAME`,
       CASE
         WHEN count(CASE column_name
                      WHEN '<column>' THEN
                        1
                    END) > 0 THEN
           'TRUE'
         ELSE
           'FALSE'         
       END `HAS COLUMN`
       FROM information_schema.columns
       GROUP BY table_name;