How to search for a specific column name in all the tables in MySQL Workbench?

Question

In MySQL Workbench, is it possible to search for a specific column name in all the tables?

(Writing the string to look for in the field at the top right does nothing).

Thank you.

Answer 1

You can use the INFORMATION_SCHEMA database and the COLUMNS table in particular Example of use:

SELECT 
    table_name, 
    column_name, 
    data_type,
    ordinal_position

FROM  INFORMATION_SCHEMA.COLUMNS 

WHERE table_schema = 'myDatabase'     --- the database you want to search 
  AND column_name = 'name' ;          --- or: column_name LIKE '%name%' 

Answer 2

To expand on @ypercube's answer (He gets a +1), if you do not know which database the table resides, do this:

SELECT 
    table_schema,
    table_name, 
    column_name, 
    data_type,
    ordinal_position

FROM  INFORMATION_SCHEMA.COLUMNS 

WHERE column_name = 'name' ;          --- or: column_name LIKE '%name%' 

Answer 3

In MySQL Workbench (v6.3) (Windows):

• Right-click any table.
• Left-click "Table Maintenance ..." after a delay...
• Left-click "Columns" tab.

This shows a sortable grid of Table, Column, ...

• Left-Click "Column" in the grid header to sort all column names, irrespective of table. So columns of the same name together.

Sadly the sort is not stable. So initially sorting by table, then column does not preserve table name ordering within a group of identical column names.

The grid is slow to open, but then it fast to find groups of columns.

It does not search across databases.