列出共享完全相同列名的所有表

I am somewhat of a novice to SQL, but have been playing around with it using SQLFiddle to get better at it. What if you wanted to find and list all the tables that have common columns or columns that are shared across the different tables (e.g. table 1, 2, 3, & 4 have a customer field, while table 2 & 4 have a student field? How would you do that with MySQL? Lets say you have 100+ tables and not 2.

我有点像SQL的新手,但是一直在使用SQLFiddle来改进它。如果您想查找并列出具有在不同表*享的公共列或列的所有表(例如,表1,2,3和4具有客户字段,而表2和表4具有学生字段,该怎么办?你会如何使用MySQL?让我们说你有100多个表而不是2个。

E.g.

Table 1:

ID | Customer | Pet |

Table 2:

ID | Customer | Food | Student |

Table 3:

ID | Customer | Activity |

Table 4:

ID | Customer | Cost | Student

Expected Outcome:

Table_NAME | COLUMN_NAME

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 the 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

1 个解决方案

#1

Try:

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

Above query will give tables/column information from all schema in your database.

上面的查询将提供数据库中所有模式的表/列信息。

If you want to limit the data to a particular schema then;

如果要将数据限制为特定模式,则;

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

#1