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'.
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.
Rick James
70.6k4 gold badges40 silver badges97 bronze badges
asked Jan 22, 2013 at 14:34
0
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%'answered Jan 22, 2013 at 15:22
ypercubeᵀᴹypercubeᵀᴹ
94k13 gold badges197 silver badges293 bronze badges
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%'
answered Jan 22, 2013 at 15:43
RolandoMySQLDBARolandoMySQLDBA
175k31 gold badges303 silver badges497 bronze badges
0
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.
answered Jul 6, 2017 at 18:43
To find column names, use information_schema.columns. Following is the syntax −
select distinct table_name from information_schema.columns where column_name like '%yourSearchValue%' and table_schema=database();Let us implement the above syntax in order to find column names across various table. Here, we want only table names with a specific column name word “Client” −
mysql> select distinct table_name from information_schema.columns where column_name like '%Client%' and table_schema=database();This will produce the following output −
+----------------+ | table_name | +----------------+ | demotable449 | | demotable450 | | demotable461 | | demotable517 | | demotable529 | | demotable534 | | demotable537 | | demotable543 | | demotable547 | +----------------+ 9 rows in set (1.19 sec)Now, let us check any of the table and look for word with “Client” column name −
Updated on 09-Oct-2019 11:12:56
- Related Questions & Answers
- Given a column name how can I find which tables in a MySQL database contain that column?
- Fetch a specific column value (name) in MySQL
- Find a specific column in all the tables in a database?
- How to find the name of a column in MySQL?
- Find records with a specific last digit in column with MySQL
- MySQL random rows sorted by a specific column name?
- How can I check MySQL tables from a database in accordance with particular column/s name?
- How to select a column name with spaces in MySQL?
- How to find all tables that contains two specific columns in MySQL?
- How to identify a column with its existence in all the tables with MySQL?
- How to find specific row with a MySQL query?
- Find rows where column value ends with a specific substring in MySQL?
- Find specific records from a column with comma separated values in MySQL
- Merging 2 tables with similar column name SAP HANA database
- MySQL query to count rows with a specific column?