Scrambling client data in T-SQL

I have this whole bunch of T-SQL scripts (3 months worth!) that needs to be applied for an updated application that I needed to implement shortly. As I work on a database with pretty sensitive information and at the same time, how can I test the script with real data?? (I never get to see it, only the schema, and that's the way it should be!)

Solution: give my client a script that scrambles identifying information without upsetting the normalization of the database, and at the same time, retains other information such as fields with bit, int, smalldatetime, etc... those information don't really identifies who the client(s) are. Now I can test the "real" database!!

Let's scramble some data... need to do a couple of things:

1. I need to decide what fields I want to scramble on the client table: first name, last name, ssn, and perhaps their phone number.

2. let's create a view holding the Random Number
SELECT RAND() as RandNumber

3. let's create a function to plug in random text:
CREATE FUNCTION spGenerateText(@Length int)
RETURNS varchar(32)

DECLARE @RandomID varchar(32)
DECLARE @counter smallint
DECLARE @RandomNumber float
DECLARE @RandomNumberInt tinyint
DECLARE @CurrentCharacter varchar(1)
DECLARE @ValidCharacters varchar(255)
SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
DECLARE @ValidCharactersLength int
SET @ValidCharactersLength = len(@ValidCharacters)
SET @CurrentCharacter = ''
SET @RandomNumber = 0
SET @RandomNumberInt = 0
SET @RandomID = ''

SET @counter = 1
WHILE @counter BEGIN
SET @RandomNumber = (SELECT RandNumber FROM vRandNumber) SET @RandomNumberInt = Convert(tinyint, ((@ValidCharactersLength - 1) * @RandomNumber + 1))
SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1)
SET @counter = @counter + 1 SET @RandomID = @RandomID + @CurrentCharacter


To run this function, I would use Sql Server Management Studio Express and run the following: SELECT dbo.sp

My sample returned:
DqFNE (seems random enough to me!)

I also did the same thing for numbers only, creating a GenerateNumber function while changing the @ValidCharacters, to be " SET @ValidCharacters = '0123456789' ".

See... you can adjust @ValidCharacters to be ANYTHING you want!

4. Let's loop through my client table and scramble it. The table has all the usual suspects: clientid, cfname, clname, phone, ssn.

Declare @id Varchar(20)
Select @id = Min(clientid) from client
While @id IS NOT NULL
update client
set clname = dbo.spGenerateText(10),
cfname = dbo.sp
phone = dbo.spGenerateNumber(10),
ssn = dbo.sp
Where clientid = @id

Select @id = min(clientid) from client where clientid > @id

5. See, job done!
SELECT clname, cfname, phone, ssn FROM Client

References (giving out credit to whom they're due!):

Edit 12/26/2007:
Thanks to Joseph Miller for pointing out my failure to add the vRandNumber view. [added new #2 above]