The query below finds all tables that have a specified column name.

See also tables that don't have a column with specific name.

Query

select tab.table_schema as database_name,
    tab.table_name
from information_schema.tables as tab
inner join information_schema.columns as col
        on col.table_schema = tab.table_schema
            and col.table_name = tab.table_name
where tab.table_type = 'BASE TABLE'
        and column_name = 'idcity'
order by tab.table_schema,
         tab.table_name;

Columns

• database_name - name of the database (schema) where the table was found
• table_name - name of the table found

Rows

• One row: represents a table
• Scope of rows: all tables found
• Ordered by: database (schema) name, table name

Sample results

List of tables that have the column 'idcity'.