SQL Server无约束数据库生成ERD：筛选同名同数据类型列查询问题

优先推荐窗口函数实现版本，逻辑简洁性能更好：

WITH ColumnBaseInfo AS (
    SELECT 
        schema_name(tab.schema_id) AS schema_name,
        tab.name AS table_name,
        col.name AS column_name,
        t.name AS data_type,
        SUM([Partitions].[rows]) AS [TotalRowCount],
        -- 按列名+数据类型分组，统计对应出现的表数量
        COUNT(DISTINCT tab.object_id) OVER(PARTITION BY col.name, t.name) AS table_count
    FROM sys.tables AS tab
    INNER JOIN sys.columns AS col ON tab.object_id = col.object_id
    LEFT JOIN sys.types AS t ON col.user_type_id = t.user_type_id
    JOIN sys.partitions AS [Partitions] ON tab.[object_id] = [Partitions].[object_id]
        AND [Partitions].index_id IN (0,1)
    GROUP BY schema_name(tab.schema_id), tab.name, col.name, t.name
)
SELECT schema_name, table_name, column_name, data_type, TotalRowCount
FROM ColumnBaseInfo
WHERE table_count >= 2
ORDER BY column_name, table_name

• 新增CTE封装原查询逻辑，通过窗口函数统计每一组「列名+数据类型」对应的不同表数量
• 最终过滤仅保留表数量≥2的记录，自动剔除仅在单张表中存在的列
• 新增按表名排序规则，方便你直接查看同一潜在关联列对应的所有表

如果不习惯用CTE，也可以用EXISTS子查询实现，逻辑完全等价：

SELECT 
    schema_name(tab.schema_id) AS schema_name,
    tab.name AS table_name,
    col.name AS column_name,
    t.name AS data_type,
    SUM([Partitions].[rows]) AS [TotalRowCount]
FROM sys.tables AS tab
INNER JOIN sys.columns AS col ON tab.object_id = col.object_id
LEFT JOIN sys.types AS t ON col.user_type_id = t.user_type_id
JOIN sys.partitions AS [Partitions] ON tab.[object_id] = [Partitions].[object_id]
    AND [Partitions].index_id IN (0,1)
WHERE EXISTS (
    SELECT 1
    FROM sys.tables t2
    INNER JOIN sys.columns c2 ON t2.object_id = c2.object_id
    LEFT JOIN sys.types ty2 ON c2.user_type_id = ty2.user_type_id
    WHERE c2.name = col.name 
      AND ty2.name = t.name
      AND t2.object_id <> tab.object_id
)
GROUP BY schema_name(tab.schema_id), tab.name, col.name, t.name
ORDER BY col.name, table_name

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