T-SQL关联sys.masked_columns时返回表所有列问题排查

第一个查询返回错误结果的核心原因是2处关联逻辑错误：

• 缺失列维度的关联条件
  sys.masked_columns系统视图的每一行对应单张表的单个列的掩码配置，主键为(object_id, column_id)，分别代表表ID和列ID。你的第一个查询在关联sys.masked_columns时，仅匹配了表维度的object_id，没有匹配列ID，会触发交叉连接：只要某张表存在任意一个掩码列，该表的所有列都会和这个掩码行关联，最终结果里所有列都会显示is_masked=1，和实际情况不符。
• 表关联条件不严谨
  关联INFORMATION_SCHEMA.COLUMNS和sys.objects时仅匹配表名，没有匹配 schema 名，若数据库中存在不同 schema 下的同名表，会匹配到错误的表对象，进一步放大结果误差。

SELECT
    TABLE_SCHEMA,
    TABLE_NAME, 
    COLUMN_NAME,
    DATA_TYPE
        + CASE WHEN DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar', 'binary', 'varbinary')
                    AND CHARACTER_MAXIMUM_LENGTH > 0 
                  THEN COALESCE('(' + CONVERT(varchar, CHARACTER_MAXIMUM_LENGTH) + ')', '')
                  ELSE '' 
          END
        + CASE WHEN DATA_TYPE IN ('decimal', 'numeric') 
                   THEN COALESCE('(' + CONVERT(varchar, NUMERIC_PRECISION) + ',' + CONVERT(varchar, NUMERIC_SCALE) + ')', '')
                   ELSE '' 
          END AS Declaration_Type,
    ISNULL(m.is_masked, 0) AS is_masked,
    m.masking_function 
FROM
    INFORMATION_SCHEMA.COLUMNS c
-- 关联表对象时补充schema匹配
JOIN
    sys.objects o ON c.TABLE_NAME = o.name
    AND c.TABLE_SCHEMA = SCHEMA_NAME(o.schema_id)
-- 关联掩码列时补充列ID匹配，使用LEFT JOIN保留无掩码的列
LEFT JOIN 
    sys.masked_columns m ON o.[object_id] = m.[object_id] 
    AND c.ORDINAL_POSITION = m.column_id
ORDER BY 
    1, 2, 3

修正后会准确返回每个列的实际掩码状态，无掩码的列is_masked会显示为0，masking_function为NULL。

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