find columns in all database

mysql, sql

Solution

You can query `information_schema` to get a list of each table in your database with a matching column name:

SELECT
    TABLE_NAME, COLUMN_NAME

FROM
    information_schema.COLUMNS

WHERE
    TABLE_SCHEMA = 'SOME_DATABASE'
    AND COLUMN_NAME LIKE '%CPV%'

EDIT (selecting only the `TABLE_NAME` column) As pointed out in a comment, if you want to select only the name of the table (without the list of columns that match), you should also use the `DISTINCT` keyword - otherwise a duplicate row will be returned for the same table for each column that matches:

SELECT
    DISTINCT TABLE_NAME
FROM
    information_schema.COLUMNS
WHERE
    TABLE_SCHEMA = 'SOME_DATABASE'
    AND COLUMN_NAME LIKE '%CPV%'

Problem

If I have a database `some_database` with multiples tables like: ``` table_one table_two table_three ``` where `table_one` has `CPV_one` `table_two` has `CPV_two` `table_three` has `CPV_three` I need to find all tables that have a column like `'%CPV%'` This can be done by sql query? I want to avoid check all tables one by one.

Original source