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

我有点像SQL的新手,但是一直在使用SQLFiddle来改进它.如果您想查找并列出所有具有在不同表中共享的公共列或列的表(例如,表1,2,3和4具有客户字段,而表2和4具有学生,该怎么办？你怎么用

MySQL做到这一点？让我们说你有100个表而不是2个表.

例如.

Table 1:

ID | Customer | Pet |

Table 2:

ID | Customer | Food | Student |

Table 3:

ID | Customer | Activity |

Table 4:

ID | Customer | Cost | Student

预期结果：

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

我试过以下两个并没有给我我想要的东西：

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

最佳答案 尝试：

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