14

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.

21

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%' 
8

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%' 
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.