How to find all the tables in MySQL with specific column names in them?

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

 Answers

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';

---

More simply done in one line of SQL:

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

---

In version that do not have information_schema (older versions, or some ndb's) you can dump the table structure and search the column manually.

mysqldump -h$host -u$user -p$pass --compact --no-data --all-databases > some_file.sql

Now search the column name in some_file.sql using your preferred text editor, or use some nifty awk scripts.

---

And a simple sed script to find the column, just replace COLUMN_NAME with your's:

sed -n '/^USE/{h};/^CREATE/{H;x;s/\nCREATE.*\n/\n/;x};/COLUMN_NAME/{x;p};' <some_file.sql
USE `DATABASE_NAME`;
CREATE TABLE `TABLE_NAME` (
  `COLUMN_NAME` varchar(10) NOT NULL,

You can pipe the dump directly in sed but that's trivial.

---

If you want "To get all tables only", Then use this query:

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME like '%'
and TABLE_SCHEMA = 'tresbu_lk'

If you want "To get all tables with Columns", Then use this query:

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