Find tables with a specific column name in MySQL database

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

See also tables that don't have a column with specific name.


select tab.table_schema as database_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,


  • database_name - name of the database (schema) where the table was found
  • table_name - name of the table found


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

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.