Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Make script generic Expand / Collapse
Author
Message
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: Sunday, November 2, 2014 10:32 PM
Points: 273, Visits: 729
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



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


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:38 PM
Points: 31,368, Visits: 15,834
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, September 19, 2014 10:06 PM
Points: 50, Visits: 218
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
Post #1609250
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse