Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Make script generic Expand / Collapse
Posted Friday, August 22, 2014 12:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 13, 2016 2:36 AM
Points: 283, Visits: 747

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)
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


Post #1606129
Posted Sunday, August 24, 2014 1:53 PM



Group: Administrators
Last Login: Yesterday @ 8:38 AM
Points: 34,366, Visits: 18,586
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.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1606922
Posted Monday, September 1, 2014 1:08 AM

Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 15, 2015 2:08 AM
Points: 50, Visits: 234
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.

schemaname = c.TABLE_SCHEMA
,tablename = c.TABLE_NAME
,columnname = c.COLUMN_NAME
,sqlquery = 'select [' + c.COLUMN_NAME + '] = newid() from [' + c.TABLE_SCHEMA + '].[' + c.TABLE_NAME + ']'
--WHERE c.TABLE_SCHEMA = @schema
-- AND c.TABLE_NAME = @table
Post #1609250
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse