查询数据库中跨表重复的列名及对应表名

算力阿峰

2026-8-17

找出数据库中重复出现的列名及对应表名

问题1解决：过滤仅重复出现的列（count>1）

你之前在WHERE子句加AND count>1报错，是因为WHERE用于分组前过滤行数据，而count是分组后的聚合计算结果，无法被WHERE识别。正确做法是用HAVING子句，它专门用于分组后过滤聚合结果。

问题2解决：显示每个重复列对应的所有表名

要获取每个重复列的所有表名，需先筛选出重复的列名，再关联回原表获取对应表信息，或用窗口函数标记重复列。

最终优化查询语句

方法1：关联子查询

SELECT c.COLUMN_NAME, c.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN (
    SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA='dbName'
    GROUP BY COLUMN_NAME
    HAVING COUNT(COLUMN_NAME) > 1
) dup_cols ON c.COLUMN_NAME = dup_cols.COLUMN_NAME
WHERE c.TABLE_SCHEMA='dbName'
ORDER BY c.COLUMN_NAME, c.TABLE_NAME;

方法2：窗口函数（写法更简洁）

SELECT COLUMN_NAME, TABLE_NAME
FROM (
    SELECT COLUMN_NAME, TABLE_NAME,
           COUNT(COLUMN_NAME) OVER (PARTITION BY COLUMN_NAME) AS col_count
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA='dbName'
) t
WHERE col_count > 1
ORDER BY COLUMN_NAME, TABLE_NAME;