MSSQL Table Schema and Sensitivity Details

table_schema_sensitivity.sql

SELECT
    QUOTENAME(schema_name(o.schema_id)) + '.' + QUOTENAME(t.name) AS [table]
    ,QUOTENAME([c].[name]) AS [column_name]
    ,IIF(ic.index_id IS NOT NULL, 1, 0) AS [column_in_index]
    ,QUOTENAME([i].[name]) AS [index_name]
    ,[i].[type_desc] AS [index_type]
    ,[sc].information_type AS [info_type]
    ,[sc].label AS [sensitivity_label]
    ,[ep].name AS [properties_name]
    ,[ep].value AS [properties_value]
FROM sys.tables [t]
JOIN sys.objects [o] ON  [t].object_id = [o].object_id
JOIN sys.columns [c] ON  [t].object_id = [c].object_id
JOIN sys.types AS [ty] ON [c].user_type_id = [ty].user_type_id
LEFT JOIN sys.indexes [i] ON t.object_id = i.object_id
LEFT JOIN sys.index_columns AS [ic] on i.object_id = ic.object_id AND i.index_id = ic.index_id AND c.column_id = ic.column_id
LEFT JOIN sys.sensitivity_classifications [sc] ON t.object_id = [sc].major_id  AND c.column_id = [sc].minor_id
LEFT JOIN sys.extended_properties [ep] ON t.object_id = [ep].major_id  AND c.column_id = [ep].minor_id AND [ep].class_desc = 'OBJECT_OR_COLUMN'