0

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"?

0

1 Answer 1

1

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
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.