Mysql – How to search entire database to find tables that contain 2 columns

MySQL

This database query doesn't work:

select table_schema,
table_name,
column_name,
column_name2
from information_schema.columns 
where column_name = 'user_id' and column_name2 = 'organisation_id' 
and table_schema = 'brand_production';

I would like to search for each table in the database to see if it contains "user_id" and "organisation_id"?

Best Answer

To include views:

SELECT TABLE_NAME, 'user_id' 'oganization_id' 
    FROM 
    INFORMATION_SCHEMA.COLUMNS
    where COLUMN_NAME IN ('user_id', 'oganization_id') AND TABLE_CATALOG = '<your database name>'
    GROUP BY TABLE_NAME 
    HAVING COUNT(COLUMN_NAME) =2

To only include tables:

SELECT c.TABLE_NAME, 'user_id', 'oganization_id' 
    FROM 
    INFORMATION_SCHEMA.COLUMNS c
    JOIN INFORMATION_SCHEMA.TABLES t on t.TABLE_NAME = c.TABLE_NAME
    where c.COLUMN_NAME IN ('user_id', 'organization_id') AND t.TABLE_TYPE = 'BASE TABLE' AND c.TABLE_CATALOG = '<your database name>'
    GROUP BY c.TABLE_NAME 
    HAVING COUNT(COLUMN_NAME) =2
Related Question