SQL Server查询多表共有字段与单表独有字段方法

基础测试DDL #

以下是提问中给出的三张测试表建表语句：

--Drop Table tableA
--Drop Table tableB
--Drop Table tableC

Create table dbo.tableA
(
Id int, 
Name varchar(100),
Code varchar(5),
Address varchar(100),
RegDate datetime,
AddedBy varchar(50)
)

Create table dbo.tableB
(
Id int, 
Name varchar(100),
KeyCode varchar(5),
Address varchar(100),
RegDate datetime,
AddedBy varchar(50)
)

Create table dbo.tableC
(
Id int, 
FName varchar(100),
LName varchar(100),
Address varchar(100)
)

提问中原有的多段INTERSECT查询共有字段的写法存在冗余、扩展性差的问题，以下是两个问题的对应解决方法： #

1、高扩展性的多表共有字段查询实现 #

原有写法每增加一张待查表就要新增一段INTERSECT查询，完全可以通过元数据聚合的方式简化，不需要反复写重复逻辑。

注意：不推荐使用老旧的sysobjects、syscolumns兼容视图，这类视图是为了兼容SQL Server 2000及更早版本保留的，后续版本存在废弃风险，元数据匹配精度也不如官方推荐的系统目录视图sys.tables、sys.columns。

实现逻辑：

• 先把所有待查询的表名统一放到表变量里，后续增删待查表只需要修改这个清单即可
• 关联系统视图取出所有待查表的字段
• 按字段名分组，统计字段出现的表数量，数量等于待查表总数的就是所有表共有的字段

对应代码：

-- 待查询表清单，新增/删除待查表仅需修改此处的VALUES列表
DECLARE @CheckTables TABLE (TableName sysname);
INSERT INTO @CheckTables (TableName)
VALUES ('tableA'), ('tableB'), ('tableC');

SELECT c.name AS [共有字段名]
FROM sys.tables t
INNER JOIN sys.columns c 
    ON t.object_id = c.object_id
INNER JOIN @CheckTables ct 
    ON t.name = ct.TableName
WHERE t.schema_id = SCHEMA_ID('dbo') -- 固定schema，避免不同schema下同表名干扰结果
GROUP BY c.name
HAVING COUNT(DISTINCT t.name) = (SELECT COUNT(*) FROM @CheckTables);

针对示例中的三张表，运行上述代码会返回共有的两个字段：Id、Address，和原有INTERSECT写法的结果完全一致，但扩展性提升明显，哪怕要查几十上百张表，也只需要修改表名清单。 #

2、单表独有字段的准确查询方法 #

之前用EXCEPT查询结果不准，通常是两个原因导致：一是没有加schema过滤，匹配到了其他schema下的同名字段；二是EXCEPT逻辑没有覆盖所有待查表，只排除了部分表的字段。

同样基于上述的待查表查表清单，用聚合逻辑就能准确查出独有字段：分组统计后，字段只在1张表中出现，就属于对应表的独有字段。

对应代码：

DECLARE @CheckTables TABLE (TableName sysname);
INSERT INTO @CheckTables (TableName)
VALUES ('tableA'), ('tableB'), ('tableC');

-- 查询所有待查表各自的独有字段
SELECT
    t.name AS [所属表],
    c.name AS [独有字段名]
FROM sys.tables t
INNER JOIN sys.columns c 
    ON t.object_id = c.object_id
INNER JOIN @CheckTables ct 
    ON t.name = ct.TableName
WHERE t.schema_id = SCHEMA_ID('dbo')
GROUP BY t.name, c.name
HAVING COUNT(DISTINCT t.name) = 1
ORDER BY t.name, c.name;

-- 如果只需要查询某一张指定表（比如tableA）的独有字段，直接增加过滤条件即可
SELECT c.name AS [tableA独有字段]
FROM sys.tables t
INNER JOIN sys.columns c 
    ON t.object_id = c.object_id
INNER JOIN @CheckTables ct 
    ON t.name = ct.TableName
WHERE t.schema_id = SCHEMA_ID('dbo')
GROUP BY c.name
HAVING 
    COUNT(DISTINCT t.name) = 1
    AND MAX(CASE WHEN t.name = 'tableA' THEN 1 ELSE 0 END) = 1;

针对示例中的三张表，查询返回的正确独有字段为：

• tableA独有：Code
• tableB独有：KeyCode
• tableC独有：FName、LName

补充说明 #

如果需要判断字段不仅名称相同、数据类型也完全一致，只需要在GROUP BY子句中增加c.system_type_id, c.max_length, c.precision, c.scale这些字段属性参数即可，避免同名字段类型不同被误判为匹配。 #

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