我的目標是撰寫腳本并匯出動態資料屏蔽。
為此，我創建了一個查詢，允許我動態創建代碼：
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;
問題是這段代碼在 AdventureWorks 上的作業效率只有一半，它回傳的資料型別類似于Name或Phone不是該列的實際資料型別：
| 模式名稱 | 表名 | 列名 | is_masked | 型別 | masking_function | （無列名） |
|---|---|---|---|---|---|---|
| 人 | 人 | 名 | 1 | 姓名 | 部分（2，“xxxx”，0） | ALTER TABLE Person.Person ALTER COLUMN FirstName Name MASKED WITH (FUNCTION = 'partial(2, "xxxx", 0)'); |
| 人 | 人 | 姓 | 1 | nvarchar(50) | 默認（） | ALTER TABLE Person.Person ALTER COLUMN LastName nvarchar(50) MASKED WITH (FUNCTION = 'default()'); |
| 人 | 個人電話 | 電話號碼 | 1 | 電話 | 部分（5，“XXXXXXX”，0） | ALTER TABLE Person.PersonPhone ALTER COLUMN PhoneNumber Phone MASKED WITH (FUNCTION = 'partial(5, "XXXXXXX", 0)'); |
| 人 | 電子郵件地址 | 電子郵件地址 | 1 | nvarchar(50) | 電子郵件（） | ALTER TABLE Person.EmailAddress ALTER COLUMN EmailAddress nvarchar(50) MASKED WITH (FUNCTION = 'email()'); |
我有一個運行良好的查詢，但它正在連接INFORMATION_SCHEMA.COLUMNS和sys.*表，這不是最佳實踐。
為什么某些資料型別以Nameor為前綴Phone？
如何為每一列定位正確的資料型別？
uj5u.com熱心網友回復：
如果您希望查詢回傳系統資料型別nvarchar(50)（而不是用戶定義的資料型別Name），則應使用sys.types以下方式更改 JOIN ：
JOIN sys.types tp ON tp.user_type_id = mc.system_type_id
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/371562.html
標籤：sql-server 查询语句 类型 数据屏蔽 动态数据屏蔽
上一篇：保存列轉換以在SELECT中重用