MySQL Workbench - how to search all columns in every table

Question

In MySQL Workbench how do I search for a column which can be located in any schema. Basically I'm looking for a column called "notification" and I want to search all schemas for that column.

Look at this screenshot where I know for a fact there is a column named "activated_at" yet when I search for it after highlighting all my tables (using select all command also) it finds nothing. Why?

but if I run the following query its able to find the column, so why not with GUI?

    SELECT 
    table_schema,
    table_name, 
    column_name, 
    data_type,
    ordinal_position

FROM  INFORMATION_SCHEMA.COLUMNS 

WHERE column_name = 'activated_at' ; 

Answer 1

Open a new Query tab - from the File menu option choose the New Query item (second one down).

Then, it's exactly the same as in the MySQL tool.

use information_schema;
show tables;
SELECT * FROM columns;

will give the result:

Tables_in_information_schema
-----------------------------
CHARACTER_SETS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS                                <<--
COLUMN_PRIVILEGES
..
<more columns snipped>
..

For some reason, the information_schema database doesn't appear in the databases list in the GUI, but if you explicitly 'use' it, it will work.

Et voilà

Answer 2

If you know database name, you can find the column name using workbench GUI:

1. Right click on the database name and select Schema Inspector:

2. Select Columns tab on the header. There you will find all the column details with table name.

If you want to search in all the databases, then you may use information_schema.columns

 desc information_schema.columns;
+--------------------------+---------------------+------+-----+---------+-------+
| Field                    | Type                | Null | Key | Default | Extra |
+--------------------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG            | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA             | varchar(64)         | NO   |     |         |       |
| TABLE_NAME               | varchar(64)         | NO   |     |         |       |
| COLUMN_NAME              | varchar(64)         | NO   |     |         |       |
| ORDINAL_POSITION         | bigint(21) unsigned | NO   |     | 0       |       |
| COLUMN_DEFAULT           | longtext            | YES  |     | NULL    |       |
| IS_NULLABLE              | varchar(3)          | NO   |     |         |       |
| DATA_TYPE                | varchar(64)         | NO   |     |         |       |
| CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| CHARACTER_OCTET_LENGTH   | bigint(21) unsigned | YES  |     | NULL    |       |
| NUMERIC_PRECISION        | bigint(21) unsigned | YES  |     | NULL    |       |
| NUMERIC_SCALE            | bigint(21) unsigned | YES  |     | NULL    |       |
| DATETIME_PRECISION       | bigint(21) unsigned | YES  |     | NULL    |       |
| CHARACTER_SET_NAME       | varchar(32)         | YES  |     | NULL    |       |
| COLLATION_NAME           | varchar(32)         | YES  |     | NULL    |       |
| COLUMN_TYPE              | longtext            | NO   |     | NULL    |       |
| COLUMN_KEY               | varchar(3)          | NO   |     |         |       |
| EXTRA                    | varchar(30)         | NO   |     |         |       |
| PRIVILEGES               | varchar(80)         | NO   |     |         |       |
| COLUMN_COMMENT           | varchar(1024)       | NO   |     |         |       |
| GENERATION_EXPRESSION    | longtext            | NO   |     | NULL    |       |
+--------------------------+---------------------+------+-----+---------+-------+
21 rows in set (0.16 sec)

Use this query to find the column notification, this will give you database name, table name and column name.

select table_schema, table_name, column_name from information_schema.columns where column_name like '%notification%';

I hope this will help you!!