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;