查询sys.types返回错误数据类型 如何获取动态数据掩码列真实类型

问题根因 #

AdventureWorks 数据库中存在大量用户自定义别名数据类型，Name、Phone 都属于这类基于原生 SQL Server 数据类型封装的自定义类型。你当前的查询通过 user_type_id 关联 sys.types 表，对于使用了自定义别名类型的列，返回的 tp.name 是别名类型的名称，而非底层的原生数据类型，因此才会出现非预期的类型值。

修复方案 #

调整 sys.types 的关联逻辑，直接取列对应的原生系统类型的属性即可，修改后的完整查询如下：

SELECT 
schema_name(tbl.schema_id) AS schema_name,
tbl.name as table_name, 
mc.name AS column_name, 
mc.is_masked
, [Type]         = 
    CASE 
      WHEN base_tp.[name] IN ('varchar', 'char') THEN base_tp.[name] + '(' + IIF(mc.max_length = -1, 'max', CAST(mc.max_length AS VARCHAR(25))) + ')' 
      WHEN base_tp.[name] IN ('nvarchar','nchar') THEN base_tp.[name] + '(' + IIF(mc.max_length = -1, 'max', CAST(mc.max_length / 2 AS VARCHAR(25)))+ ')'      
      WHEN base_tp.[name] IN ('decimal', 'numeric') THEN base_tp.[name] + '(' + CAST(mc.[precision] AS VARCHAR(25)) + ', ' + CAST(mc.[scale] AS VARCHAR(25)) + ')'
      WHEN base_tp.[name] IN ('datetime2') THEN base_tp.[name] + '(' + CAST(mc.[scale] AS VARCHAR(25)) + ')'
      ELSE base_tp.[name]
    END,
mc.masking_function
,'ALTER TABLE '
+ schema_name(tbl.schema_id) + '.' + tbl.name +
' ALTER COLUMN '
+ mc.name +  ' ' + 
    CASE 
      WHEN base_tp.[name] IN ('varchar', 'char') THEN base_tp.[name] + '(' + IIF(mc.max_length = -1, 'max', CAST(mc.max_length AS VARCHAR(25))) + ')' 
      WHEN base_tp.[name] IN ('nvarchar','nchar') THEN base_tp.[name] + '(' + IIF(mc.max_length = -1, 'max', CAST(mc.max_length / 2 AS VARCHAR(25)))+ ')'      
      WHEN base_tp.[name] IN ('decimal', 'numeric') THEN base_tp.[name] + '(' + CAST(mc.[precision] AS VARCHAR(25)) + ', ' + CAST(mc.[scale] AS VARCHAR(25)) + ')'
      WHEN base_tp.[name] IN ('datetime2') THEN base_tp.[name] + '(' + CAST(mc.[scale] AS VARCHAR(25)) + ')'
      ELSE base_tp.[name]
    END
+ ' MASKED WITH (FUNCTION = ''' 
+ CAST(mc.masking_function COLLATE Latin1_General_CI_AI AS nvarchar(100))
+ ''');'

FROM sys.masked_columns AS mc  
JOIN sys.tables AS tbl ON mc.[object_id] = tbl.[object_id]  
-- 关联原生系统类型，而非用户自定义别名类型
JOIN sys.types base_tp ON mc.system_type_id = base_tp.system_type_id AND base_tp.is_user_defined = 0
WHERE mc.is_masked = 1; 

修复后生成的ALTER语句会自动将别名类型替换为对应的原生数据类型，可直接执行，不需要依赖原库的自定义类型定义。

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