August 22, 2014 at 12:48 am
Requirement-
I have 200+ columns whose data needs to be replaced with some random values i.e. mask the data.
The length of data type usually -varchar.
My query looks like - where column1 is being masked.
select column1,
substring (column1,1,2)+LEFT(REPLICATE(replace(newid(),'-',''),1+LEN(column1)/32),LEN(column1)-2)
from
schema1.table1
where len(column1) > 30
How do I make function (or something else )to make it more generic so that I can pass column, schema and table name more generically?
(since they all will change)
or is it worth making one?
Thanks for help
Khushbu
August 24, 2014 at 1:53 pm
You can't use a variable in the FROM clause.
However if this is for something like masking data on restores to dev/test, and isn't run often, you can use dynamic-SQL
declare @cmd varchar(max)
declare @table1 varchar(200)
select @table1 = 'table1'
select @cmd = ' select column1, substring (column1,1,2) + '
+ 'LEFT(REPLICATE(replace(newid(),''-'',''''),1+LEN(column1)/32),LEN(column1)-2) '
+ 'from schema1.' + @table1
+ 'where len(column1) > 30'
Note that you can also make variables for the column and schema.
September 1, 2014 at 1:08 am
I'm not sure how you can avoid creating a function/procedure or template.
I suggest using INFORMATION_SCHEMA views to generate the dynamic SQL.
Here's some sample SQL, but you'll probably need to CAST the "newid()" or a set of case statements to match your random value to match the appropriate datatype.
SELECT
schemaname = c.TABLE_SCHEMA
,tablename = c.TABLE_NAME
,columnname = c.COLUMN_NAME
,c.DATA_TYPE
,c.CHARACTER_MAXIMUM_LENGTH
,sqlquery = 'select [' + c.COLUMN_NAME + '] = newid() from [' + c.TABLE_SCHEMA + '].[' + c.TABLE_NAME + ']'
FROM INFORMATION_SCHEMA.COLUMNS c
--WHERE c.TABLE_SCHEMA = @schema
--AND c.TABLE_NAME = @table
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply