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.
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?
SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%wild%';
To get all tables with columns
ColumnB in the database
SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('columnA','ColumnB') AND TABLE_SCHEMA='YourDatabase';
SELECT DISTINCT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE column_name LIKE 'employee%' AND TABLE_SCHEMA='YourDatabase'
More simply done in one line of SQL:
SELECT * FROM information_schema.columns WHERE column_name="column_name";