sys.types returns wrong data type-CodePudding

My goal is to script out and export Dynamic Data Masking.

In order to do so I have created a query that allows me to create the code dynamically:

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 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,
mc.masking_function
,'ALTER TABLE '
  schema_name(tbl.schema_id)   '.'   tbl.name  
' ALTER COLUMN '
  mc.name    ' '   
    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
  ' MASKED WITH (FUNCTION = ''' 

--   mc.masking_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 tp ON mc.user_type_id = tp.user_type_id
WHERE mc.is_masked = 1;

The problem is that this code is half working as on AdventureWorks it returns me data type like Name or Phone that are not the actual data type of that column:

schema_name	table_name	column_name	is_masked	Type	masking_function	(No column name)
Person	Person	FirstName	1	Name	partial(2, "xxxx", 0)	ALTER TABLE Person.Person ALTER COLUMN FirstName Name MASKED WITH (FUNCTION = 'partial(2, "xxxx", 0)');
Person	Person	LastName	1	nvarchar(50)	default()	ALTER TABLE Person.Person ALTER COLUMN LastName nvarchar(50) MASKED WITH (FUNCTION = 'default()');
Person	PersonPhone	PhoneNumber	1	Phone	partial(5, "XXXXXXX", 0)	ALTER TABLE Person.PersonPhone ALTER COLUMN PhoneNumber Phone MASKED WITH (FUNCTION = 'partial(5, "XXXXXXX", 0)');
Person	EmailAddress	EmailAddress	1	nvarchar(50)	email()	ALTER TABLE Person.EmailAddress ALTER COLUMN EmailAddress nvarchar(50) MASKED WITH (FUNCTION = 'email()');

I had a query that was working nicely but it was joining INFORMATION_SCHEMA.COLUMNS and sys.* tables and this is not best practice.

Why some data type are prefixed with Name or Phone?

How to target the right data type for each column?

CodePudding user response：

If you want the query to return the system data type nvarchar(50) (instead of the user-defined data type Name), you should change the JOIN with sys.types this way:

JOIN sys.types tp ON tp.user_type_id = mc.system_type_id