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|
|class_desc||varchar(16)||A description of the class of the item on which the classification exists|
|major_id||int||ID of the item on which the classification exists.<br ><br >If class is 0, major_id is always 0.
If class is 1, 2, or 7 major_id is object_id.
|minor_id||int||Secondary ID of the item on which the classification exists, interpreted according to its class.
If class = 1, minor_id is the column_id (if column), else 0 (if object).
If class = 2, minor_id is the parameter_id.
If class = 7, minor_id is the index_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)|
- 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. Consequently:
- class - will always have the value 1 (representing a column)
- class_desc - will always have the value OBJECT_OR_COLUMN
- major_id - represents the ID of the table containing the classified column, corresponding with sys.all_objects.object_id
- minor_id - represents the ID of the column on which the classification exists, corresponding with sys.all_columns.column_id
A. Listing all classified columns and their corresponding classification
The following example returns a table listing the table name, column name, label, label ID, information type, information type ID for each classified column in the database.
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] 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
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.
Send feedback about: