Mysql show tables with column name

Question

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

Table of Contents Show

Sample results
How do I find all the tables with the column name of?
How can check column name in all tables in MySQL?
How do I show columns in a MySQL table?
How do you check if a table has a column MySQL?

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?

How do I find all the tables with the column name of?

Use this Query to search Tables & Views:.

SELECT COL_NAME AS 'Column_Name', TAB_NAME AS 'Table_Name'.

FROM INFORMATION_SCHEMA.COLUMNS..

WHERE COL_NAME LIKE '%MyName%'.

ORDER BY Table_Name, Column_Name;.

How can check column name in all tables in MySQL?

How to list all tables that contain a specific column name in MySQL? You want to look for tables using the name of columns in them. SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA. COLUMNS WHERE COLUMN_NAME IN('column1', 'column2') AND TABLE_SCHEMA = 'schema_name';

How do I show columns in a MySQL table?

Syntax. The following is a syntax to display the column information in a specified table: SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS} {FROM | IN} table_name.

How do you check if a table has a column MySQL?

Find if the column exists using the SQL below: SELECT column_name FROM INFORMATION_SCHEMA . COLUMNS WHERE TABLE_SCHEMA =[Database Name] AND TABLE_NAME =[Table Name]; If the above query returns a result then it means the column exists, otherwise you can go ahead and create the column.

kode mysql MySQL DESCRIBE