Your latest bookmark from Email This How to find all the tables in MySQL with specific column names in them? 🔥 Save unlimited web pages along with a full PDF snapshot of each page. Unlock Premium → 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? ajreal 44.1k1010 gold badges7878 silver badges116116 bronze badges asked Oct 11 '08 at 7:02 Jobi JoyJobi Joy 44.6k2020 gold badges104104 silver badges119119 bronze badges 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'; answered Oct 11 '08 at 8:42 KenKen 68.8k2929 gold badges8181 silver badges9999 bronze badges SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%wild%'; JayRizzo 1,67422 gold badges2121 silver badges3333 bronze badges answered Oct 11 '08 at 7:06 GSergGSerg 68.7k1616 gold badges132132 silver badges277277 bronze badges More simply done in one line of SQL: SELECT * FROM information_schema.columns WHERE column_name = 'column_name'; Craig Ringer 242k5353 gold badges538538 silver badges642642 bronze badges answered Oct 9 '12 at 10:46 Xman ClassicalXman Classical 4,02411 gold badge2222 silver badges2424 bronze badges SELECT DISTINCT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE column_name LIKE 'employee%' AND TABLE_SCHEMA='YourDatabase' Yi Jiang 45.3k1414 gold badges129129 silver badges130130 bronze badges answered Dec 11 '10 at 3:37 baycaysoibaycaysoi 38933 silver badges33 bronze badges 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. 5377037 8,9121212 gold badges3939 silver badges7070 bronze badges answered Aug 14 '12 at 13:01 Radu MarisRadu Maris 5,17833 gold badges3535 silver badges5353 bronze badges For those searching for the inverse of this, i.e. looking for tables that do not contain a certain column name, here is the query... SELECT DISTINCT TABLE_NAME FROM information_schema.columns WHERE TABLE_SCHEMA = 'your_db_name' AND TABLE_NAME NOT IN (SELECT DISTINCT TABLE_NAME FROM information_schema.columns WHERE column_name = 'column_name' AND TABLE_SCHEMA = 'your_db_name'); This came in really handy when we began to slowly implement use of InnoDB's special ai_col column and needed to figure out which of our 200 tables had yet to be upgraded. answered Aug 20 '15 at 10:06 ouciloucil 3,26711 gold badge2828 silver badges4545 bronze badges 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' 5377037 8,9121212 gold badges3939 silver badges7070 bronze badges answered Dec 11 '15 at 12:42 Use this one line query, replace desired_column_name by your column name. SELECT TABLE_NAME FROM information_schema.columns WHERE column_name = 'desired_column_name'; answered Sep 19 '17 at 15:44 SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%city_id%' AND TABLE_SCHEMA='database' answered May 17 '18 at 5:50 nageen nayaknageen nayak 1,03722 gold badges1515 silver badges2020 bronze badges The problem with information_schema is that it can be terribly slow. It is faster to use the SHOW commands. After you select the database you first send the query SHOW TABLES. And then you do SHOW COLUMNS for each of the tables. In PHP that would look something like $res = mysqli_query("SHOW TABLES"); while($row = mysqli_fetch_array($res)) { $rs2 = mysqli_query("SHOW COLUMNS FROM ".$row[0]); while($rw2 = mysqli_fetch_array($rs2)) { if($rw2[0] == $target) .... } } answered Feb 22 '19 at 11:39 select distinct table_name from information_schema.columns where column_name in ('ColumnA') and table_schema='YourDatabase'; and table_name in ( select distinct table_name from information_schema.columns where column_name in ('ColumnB') and table_schema='YourDatabase'; ); That ^^ will get the tables with ColumnA AND ColumnB instead of ColumnA OR ColumnB like the accepted answer answered Dec 17 '19 at 22:10 love2codelove2code 34444 silver badges88 bronze badges Not the answer you're looking for? Browse other questions tagged mysql information-schema or ask your own question. Source: https://stackoverflow.com/questions/193780/how-to-find-all-the-tables-in-mysql-with-specific-column-names-in-them This web page was saved on Tuesday, Sep 22 2020. Upgrade to Premium Plan ✔ Save unlimited bookmarks. ✔ Get a complete PDF copy of each web page ✔ Save PDFs, DOCX files, images and Excel sheets as email attachments. ✔ Get priority support and access to latest features. Upgrade now → © Email This