Skip to content

Latest commit

 

History

History
55 lines (46 loc) · 3.24 KB

sys-masked-columns-transact-sql.md

File metadata and controls

55 lines (46 loc) · 3.24 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
sys.masked_columns (Transact-SQL)
sys.masked_columns (Transact-SQL)
rwestMSFT
randolphwest
05/25/2021
sql
system-objects
reference
sys.masked_columns
masked_columns_tsql
sys.masked_columns_tsql
masked_columns
sys.masked_columns catalog view
TSQL
=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

sys.masked_columns (Transact-SQL)

[!INCLUDE sqlserver2016-asdb-asdbmi]

Use the sys.masked_columns view to query for table-columns that have a dynamic data masking function applied to them. This view inherits from the sys.columns view. It returns all columns in the sys.columns view, plus the is_masked and masking_function columns, indicating if the column is masked, and if so, what masking function is defined. This view only shows the columns on which there is a masking function applied.

Column name Data type Description
object_id int ID of the object to which this column belongs.
name sysname Name of the column. Is unique within the object.
column_id int ID of the column. Is unique within the object.

Column IDs might not be sequential.
sys.masked_columns returns many more columns inherited from sys.columns. various See sys.columns (Transact-SQL) for more column definitions.
is_masked bit Indicates if the column is masked. 1 indicates masked.
masking_function nvarchar(4000) The masking function for the column.
generated_always_type tinyint Applies to: [!INCLUDEsssql16-md] and later, [!INCLUDEssSDS_md]. 7, 8, 9, 10 only applies to [!INCLUDEssSDS_md].

Identifies when the column value is generated (will always be 0 for columns in system tables):

0 = NOT_APPLICABLE
1 = AS_ROW_START
2 = AS_ROW_END
7 = AS_TRANSACTION_ID_START
8 = AS_TRANSACTION_ID_END
9 = AS_SEQUENCE_NUMBER_START
10 = AS_SEQUENCE_NUMBER_END

For more information, see Temporal Tables (Relational databases).

Permissions

This view returns information about tables where the user has some sort of permission on the table or if the user has the VIEW ANY DEFINITION permission.

Example

The following query joins sys.masked_columns to sys.tables to return information about all masked columns.

SELECT tbl.name as table_name, c.name AS column_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;  

See Also

Dynamic Data Masking
sys.columns (Transact-SQL)