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, 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;
- 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
List of tables that have the column 'idcity'.