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
CREATE VIEW vRandNumber
AS
SELECT RAND() as RandNumber
GO

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

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
END

RETURN @RandomID
END
GO


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

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
Begin
update client
set clname = dbo.spGenerateText(10),
cfname = dbo.sp
GenerateText(8),
phone = dbo.spGenerateNumber(10),
ssn = dbo.sp
GenerateNumber(9)
Where clientid = @id

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

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

References (giving out credit to whom they're due!):
http://www.extremeexperts.com/SQL/Articles/IterateTSQLResult.aspx
http://www.brettb.com/SQLHelpRandom_Strings.asp

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