Find tables with a specific column name in MariaDB 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.

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
            and column_name = 'your column name'
where tab.table_type = 'BASE TABLE'
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 one table
  • Scope of rows: all tables found
  • Ordered by: database (schema) name, table name

Sample results

List of tables that have the column 'idcity'

0
There are no comments. Click here to write the first comment.