Home > OS >  sys.types returns wrong data type
sys.types returns wrong data type

Time:12-01

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()');

enter image description here

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
  • Related