sys.sensitivity_classifications (Transact-SQL)

Applies to: yesSQL Server (all supported versions) YesAzure SQL Database YesAzure SQL Managed Instance yesAzure 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 SQL Data Warehouse.

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 has been 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