SET NOCOUNT ON
GO
DECLARE dataclassification CURSOR FOR
SELECT SCHEMA_NAME(schema_id) AS schema_name, t.name AS table_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
where c.name not in ( SELECT
C.NAME AS column_name
FROM sys.sensitivity_classifications sc
JOIN sys.objects O
ON sc.major_id = O.object_id
JOIN sys.columns C
ON sc.major_id = C.object_id AND sc.minor_id = C.column_id)
OPEN dataclassification
DECLARE @tableSchema NVARCHAR(128)
DECLARE @tableName NVARCHAR(128)
DECLARE @Columnname NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)
FETCH NEXT FROM dataclassification INTO @tableSchema, @tableName, @columnname
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @Statement = 'ADD SENSITIVITY CLASSIFICATION TO ' + '[' + @tableSchema + ']' + '.' + '[' + @tableName + ']' + '.' + '[' + @Columnname + ']'
+ 'WITH ( LABEL=''General'' , Information_type =''Other'')' --Here you can specify the specific senstivity label which you want your column to get added.
PRINT @Statement -- comment this print statement to prevent it from printing whenever you are ready to execute the command below.
--EXEC sp_executesql @Statement -- remove the comment on the beginning of this line to run the commands
FETCH NEXT FROM dataclassification INTO @tableSchema, @tableName,@columnname
END
CLOSE dataclassification
DEALLOCATE dataclassification
GO
SET NOCOUNT OFF
GO
https://docs.microsoft.com/en-us/azure/azure-sql/database/data-discovery-and-classification-overview
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.