List all tables with the same column names

Question

I was somehow new to SQL, but played with it using SQLFiddle to get better. What if you want to find and list all the tables that have common columns or columns that are shared in different tables (for example, tables 1, 2, 3 and 4 have a client field, and in table 2 and 4 there is a field for students ? How do you do this with MySQL? Let's say you have 100+ tables, not 2.

eg.

Table 1:

ID | Customer | Pet |

Table 2:

ID | Customer | Food | Student |

Table 3:

ID | Customer | Activity |

Table 4:

ID | Customer | Cost | Student

Expected Result:

Table_NAME | COLUMN_NAME

Table 1, Table 2, Table 3, Table 4 | ID

Table 1, Table 2, Table 3, Table 4 | CUSTOMER

Table 2, Table 4 | Student

I tried both below and did not give me what I was looking for:

select Table_NAME, COLUMN_NAME, Count(*)
from INFORMATION_SCHEMA.columns
GROUP BY Table_NAME
HAVING COUNT(*) > 1


SELECT Table_Name, Column_Name
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME IN
(     SELECT TABLE_NAME
      FROM INFORMATION_SCHEMA.columns
      GROUP BY TABLE_NAME
      HAVING COUNT(*) > 1
 )
 ORDER BY TABLE_NAME

Answer 1

Try:

select GROUP_CONCAT(TABLE_NAME) TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
GROUP BY COLUMN_NAME
HAVING COUNT(*) > 1

/ .

,

select GROUP_CONCAT(TABLE_NAME) TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'your_schema'
GROUP BY COLUMN_NAME
HAVING COUNT(*) > 1