How can I find all the tables in MySQL with specific column names in them? – Dev

The best answers to the question “How can I find all the tables in MySQL with specific column names in them?” in the category Dev.

QUESTION:

I have 2-3 different column names that I want to look up in the entire database and list out all tables which have those columns. Is there any easy script?

ANSWER:

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%wild%';

ANSWER:

To get all tables with columns columnA or ColumnB in the database YourDatabase:

SELECT DISTINCT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('columnA','ColumnB')
        AND TABLE_SCHEMA='YourDatabase';

ANSWER:

SELECT DISTINCT TABLE_NAME, COLUMN_NAME  
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE column_name LIKE 'employee%'  
AND TABLE_SCHEMA='YourDatabase'

ANSWER:

More simply done in one line of SQL:

SELECT * FROM information_schema.columns WHERE column_name="column_name";