How to search entire database to find tables that contain 2 columns? 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 &quot;user_id&quot; and &quot;organisation_id&quot;?
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 = '&lt;your database name&gt;'
    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 = '&lt;your database name&gt;'
    GROUP BY c.TABLE_NAME 
    HAVING COUNT(COLUMN_NAME) =2