sys.sensitivity_classifications (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

Returns a row for each classified item in the database.

Column name	Data type	Description
class	int	Identifies the class of the item on which the classification exists. Will always have the value 1 (representing a column)
class_desc	varchar(16)	A description of the class of the item on which the classification exists. will always have the value OBJECT_OR_COLUMN
major_id	int	Represents the ID of the table containing the classified column, corresponding with sys.all_objects.object_id
minor_id	int	Represents the ID of the column on which the classification exists, corresponding with sys.all_columns.column_id
label	sysname	The label (human readable) assigned for the sensitivity classification
label_id	sysname	An ID associated with the label, which can be used by an information protection system such as Azure Information Protection (AIP)
information_type	sysname	The information type (human readable) assigned for the sensitivity classification
information_type_id	sysname	An ID associated with the information type, which can be used by an information protection system such as Azure Information Protection (AIP)
rank	int	A numerical value of the rank: 0 for NONE 10 for LOW 20 for MEDIUM 30 for HIGH 40 for CRITICAL
rank_desc	sysname	Textual representation of the rank: NONE, LOW, MEDIUM, HIGH, CRITICAL

Remarks

• This view provides visibility into the classification state of the database. It can be used for managing the database classifications, as well as for generating reports.
• Currently only classification of database columns is supported.

Examples

A. Listing all classified columns and their corresponding classification

The following example returns a table that lists the table name, column name, label, label ID, information type, information type ID, rank, and rank description for each classified column in the database.

Note

Label is a keyword for Azure Synapse Analytics.

SELECT
    SCHEMA_NAME(sys.all_objects.schema_id) as SchemaName,
    sys.all_objects.name AS [TableName], sys.all_columns.name As [ColumnName],
    [Label], [Label_ID], [Information_Type], [Information_Type_ID], [Rank], [Rank_Desc]
FROM
          sys.sensitivity_classifications
left join sys.all_objects on sys.sensitivity_classifications.major_id = sys.all_objects.object_id
left join sys.all_columns on sys.sensitivity_classifications.major_id = sys.all_columns.object_id
                         and sys.sensitivity_classifications.minor_id = sys.all_columns.column_id

Permissions

Requires the VIEW ANY SENSITIVITY CLASSIFICATION permission.

The visibility of the metadata in catalog views is limited to securables that a user either owns, or on which the user was granted some permission. For more information, see Metadata Visibility Configuration.

See Also

ADD SENSITIVITY CLASSIFICATION (Transact-SQL)

DROP SENSITIVITY CLASSIFICATION (Transact-SQL)

Getting started with SQL Information Protection