SQL中如何用动态查询找出两张表的相同列名？

要找出两张表的共同列名，无需复杂逻辑，直接查询数据库的系统元数据表即可。不同数据库的具体实现如下，同时会说明你之前用INTERSECT失效的常见原因：

SQL Server #

• 直接通过sys.columns系统视图结合INTERSECT查询：

SELECT name AS common_column
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.TableA') -- 替换为你的表A名称及schema
INTERSECT
SELECT name
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.TableB'); -- 替换为你的表B名称及schema

• 若之前INTERSECT失效，大概率是未指定正确schema（比如默认的dbo）或列名大小写不匹配，可以通过 COLLATE 忽略大小写：

SELECT name COLLATE SQL_Latin1_General_CP1_CI_AS AS common_column
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.TableA')
INTERSECT
SELECT name COLLATE SQL_Latin1_General_CP1_CI_AS
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.TableB');

MySQL #

• 查询information_schema.columns系统表：

SELECT column_name AS common_column
FROM information_schema.columns
WHERE table_schema = 'your_database' -- 替换为你的数据库名
  AND table_name = 'TableA'
INTERSECT
SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'your_database'
  AND table_name = 'TableB';

• 若你的MySQL版本不支持INTERSECT，改用INNER JOIN方式：

SELECT a.column_name AS common_column
FROM information_schema.columns a
JOIN information_schema.columns b 
  ON a.column_name = b.column_name
WHERE a.table_schema = 'your_database'
  AND a.table_name = 'TableA'
  AND b.table_schema = 'your_database'
  AND b.table_name = 'TableB'
GROUP BY a.column_name;

Oracle #

• 查询user_tab_columns（当前用户的表）或all_tab_columns（有权限的所有表）：

SELECT column_name AS common_column
FROM user_tab_columns
WHERE table_name = 'TABLEA' -- Oracle表名默认大写，注意匹配实际表名
INTERSECT
SELECT column_name
FROM user_tab_columns
WHERE table_name = 'TABLEB';

• 若需要忽略大小写，用LOWER()统一转换：

SELECT LOWER(column_name) AS common_column
FROM user_tab_columns
WHERE LOWER(table_name) = 'tablea'
INTERSECT
SELECT LOWER(column_name)
FROM user_tab_columns
WHERE LOWER(table_name) = 'tableb';

动态SQL扩展（自动生成跨表查询） #

如果找到共同列后，想要自动生成查询两张表共同列的SQL，以SQL Server为例：

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);

SELECT @cols = STRING_AGG(name, ', ')
FROM (
    SELECT name
    FROM sys.columns
    WHERE object_id = OBJECT_ID('dbo.TableA')
    INTERSECT
    SELECT name
    FROM sys.columns
    WHERE object_id = OBJECT_ID('dbo.TableB')
) AS common_cols;

SET @sql = N'SELECT ' + @cols + N' FROM dbo.TableA UNION ALL SELECT ' + @cols + N' FROM dbo.TableB';
EXEC sp_executesql @sql;

内容的提问来源于stack exchange，提问作者Prasad Abhang