How to find all tables containing two specific columns in MySQL?

To find two specific column names, use information_schema.columns Here I use Id instead of columnA and Name instead of columnB -

mysql> select table_name as TableNameFromWebDatabase
   -> from information_schema.columns
   -> where column_name IN ('Id', 'Name')
   -> group by table_name
   -> having count(*) = 3;

This will produce The following output. Below is the table with Id and Name columns -

+--------------------------+
| TableNameFromWebDatabase |
+--------------------------+
| student                  |
| distinctdemo             |
| secondtable              |
| groupconcatenatedemo     |
| indemo                   |
| ifnulldemo               |
| demotable211             |
| demotable212             |
| demotable223             |
| demotable233             |
| demotable251             |
| demotable255             |
+--------------------------+
12 rows in set (0.25 sec)

To prove this, let us check the description of one of the tables. Following is the query -

mysql> desc demotable233;

which will produce the following output. Here you can see we have Int and Name columns -

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
|    Id | int(11)     | NO   | PRI | NULL    | auto_increment |
| Name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

The above is the detailed content of How to find all tables containing two specific columns in MySQL?. For more information, please follow other related articles on the PHP Chinese website!