Is it possible to alter columns by matching them to another temporary table that exist?

Question

I am working on masking personal data in several databases in SQL Datawarehouse. I have created a table with columns that i want to be masked and would like to alter these columns that exists in several tables in a database by a single script like this

IF EXISTS(SELECT *
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_NAME = 'TableName' and 
          COLUMN_NAME = 'BirthDate' OR COLUMN_NAME = 'EmailAddress' OR COLUMN_NAME = 'FirstName' OR COLUMN_NAME = 'LastName' OR COLUMN_NAME = 'MiddleName' OR COLUMN_NAME = 'Name.First' OR COLUMN_NAME = 'MName.Last' OR COLUMN_NAME = 'PhoneNumber'
          )
BEGIN
ALTER TABLE TABLE_NAME
ALTER COLUMN [COLUMN_NAME] NVARCHAR(200) MASKED WITH (FUNCTION = 'default()') NULL
END

This does not work. Any ideas?

I don't even see the point of this approach. If you want to mask certain columns, then why not just create a view (or maybe a proc) which presents your data in a secure way? — Tim Biegeleisen, Feb 18 '19 at 14:24
Hi Tim, Thanks for a quick response. The issue is not security rather gdpr. I could do an update to the columns but i was just wondering if masking is a better option. — Robi Morro, Feb 18 '19 at 14:29
I am saying just write a select which generates the output you want to see and forget about it. There are all sorts of caveats with copying your data, such as that data which _isn't_ masked can then easily become stale as its original source changes. — Tim Biegeleisen, Feb 18 '19 at 14:30

score 0 · Answer 1 · answered Feb 18 '19 at 14:29

0

You would have to do this with Dynamic SQL. Use your query of the Information_Schema to dynamically generate the ALTER TABLE statements you want, and then execute them.

answered Feb 18 '19 at 14:29

Tab Alleman

31,483
7
36
52

Hi Tab, thanks for your response. Can you please give an example? – Robi Morro Feb 18 '19 at 14:50
Check here, the example is for adding a column, but it can easily be changed to alter a column instead: https://stackoverflow.com/questions/2614101/alter-table-my-table-add-column-int – Tab Alleman Feb 18 '19 at 14:54

score 0 · Accepted Answer · answered Feb 18 '19 at 15:01

Yo need to use a loop to execute your DDL code using dynamic sql

DECLARE @tmp TABLE (ID int identity, TableName varchar(100), ColumnName varchar(100)

INSERT @tmp
SELECT QUOTENAME(TABLE_NAME), QUOTENAME(COUMN_NAME)
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_NAME = 'TableName' and 
          COLUMN_NAME = 'BirthDate' OR COLUMN_NAME = 'EmailAddress' OR COLUMN_NAME = 'FirstName' OR COLUMN_NAME = 'LastName' OR COLUMN_NAME = 'MiddleName' OR COLUMN_NAME = 'Name.First' OR COLUMN_NAME = 'MName.Last' OR COLUMN_NAME = 'PhoneNumber'
          )
DECLARE @ID int, @TableName varchar(100), @ColumnName varchar(100)
DECLARE @SQL varchar(MAX)
WHILE EXISTS (SELECT 1 FROM @tmp)
BEGIN
    SELECT TOP 1 @ID = ID, @TableName = TableName, @ColumnName = ColumnName

    SET @SQL = '
    ALTER TABLE ' + @TableName + '
    ALTER COLUMN ' + @ColumnName + ' NVARCHAR(200) MASKED WITH (FUNCTION = ''default()'') NULL
    '
    EXEC(@SQL)
    DELETE @tmp WHERE ID = @ID
END

Is it possible to alter columns by matching them to another temporary table that exist?

2 Answers2