T-SQL :: joining sys.masked_columns returns all columns in table-CodePudding

I'm trying to join INFORMATION_SCHEMA.COLUMNS and sys.masked_columns through sys.objects.

This is my query:

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,
    --CASE WHEN IS_NULLABLE='NO' THEN 'NOT ' ELSE '' END   'NULL' AS Nullable
    m.is_masked,
    m.masking_function 
FROM
    INFORMATION_SCHEMA.COLUMNS c
JOIN
    sys.objects o ON c.table_name = o.name
JOIN 
    sys.masked_columns m ON o.[object_id] = m.[object_id] 
ORDER BY 
    1, 2, 3

and returns this:

TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	Declaration_Type	is_masked	masking_function
Person	EmailAddress	BusinessEntityID	int	1	email()
Person	EmailAddress	EmailAddress	nvarchar(50)	1	email()
Person	EmailAddress	EmailAddressID	int	1	email()
Person	EmailAddress	ModifiedDate	datetime	1	email()
Person	EmailAddress	rowguid	uniqueidentifier	1	email()

But the result is wrong because it shows that all columns in Person.EmailAddress are masked.

If I check through this query:

SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function  
FROM sys.masked_columns AS c  
JOIN sys.tables AS tbl   
    ON c.[object_id] = tbl.[object_id]  
WHERE is_masked = 1;

SSMS only return 1 masked column:

name	table_name	is_masked	masking_function
EmailAddress	EmailAddress	1	email()

Why is returning every single column in the Person.EmailAddress?

CodePudding user response：

Thank you to @MartinSmith and @JeroenMostert for their help in the comment.

The right solution was to get rid of INFORMATION_SCHEMA.* columns and use sys. columns instead:

mc.name AS column_name, 
mc.is_masked, 
mc.masking_function
, [Type]         = 
    CASE 
      WHEN tp.[name] IN ('varchar', 'char') THEN tp.[name]   '('   IIF(mc.max_length = -1, 'max', CAST(mc.max_length AS VARCHAR(25)))   ')' 
      WHEN tp.[name] IN ('nvarchar','nchar') THEN tp.[name]   '('   IIF(mc.max_length = -1, 'max', CAST(mc.max_length / 2 AS VARCHAR(25)))  ')'      
      WHEN tp.[name] IN ('decimal', 'numeric') THEN tp.[name]   '('   CAST(mc.[precision] AS VARCHAR(25))   ', '   CAST(mc.[scale] AS VARCHAR(25))   ')'
      WHEN tp.[name] IN ('datetime2') THEN tp.[name]   '('   CAST(mc.[scale] AS VARCHAR(25))   ')'
      ELSE tp.[name]
    END
FROM sys.masked_columns AS mc  
JOIN sys.tables AS tbl ON mc.[object_id] = tbl.[object_id]  
JOIN sys.types tp ON mc.user_type_id = tp.user_type_id
WHERE mc.is_masked = 1;