Displaying Hidden ASCII codes in database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Displaying Hidden ASCII codes in database

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-11 : 08:48:07

Sam writes "Hello all,
I need to write a script to run against production server and displays data field with invisible ASCII codes.
I wrote the code using cursor in SQL 2000 but scanning 200 tables takes more than 6 hours.
Is there any other way to do this ?

DECLARE @tblName varchar(128), @colName varchar(128), @SQL varchar(8000), @badChars varchar(1000)

SET @badChars = CHAR(1) + CHAR(2) + CHAR(3) + CHAR(4) + CHAR(5) + CHAR(6) + CHAR(7) + CHAR(8) + CHAR(9) + CHAR(10) + CHAR(11) + CHAR(12) + CHAR(13) + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17) + CHAR(18) + CHAR(19) + CHAR(20) + CHAR(21) + CHAR(22) + CHAR(23) + CHAR(24) + CHAR(25) + CHAR(26) + CHAR(27) + CHAR(28) + CHAR(29) + CHAR(30) + CHAR(31)

DECLARE curFR CURSOR FOR
SELECT (SELECT name FROM sysobjects WHERE id = sc.id), sc.name FROM syscolumns sc WHERE id IN (SELECT id FROM sysobjects WHERE xtype = 'U') AND xtype IN (SELECT xtype FROM systypes WHERE name IN ('char', 'varchar'))

OPEN curFR

FETCH NEXT FROM curFR INTO
@tblName, @colName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'IF EXISTS(SELECT ''' + @tblName + ''' TblName, ''' + @colName + ''' ColName, * FROM ' + @tblName + ' WHERE ' + @colName + ' LIKE ''%[' + @badChars + ']%'')
SELECT ''' + @tblName + ''' TblName, ''' + @colName + ''' ColName, * FROM ' + @tblName + ' WHERE ' + @colName + ' LIKE ''%[' + @badChars + ']%'''
EXEC(@SQL)

FETCH NEXT FROM curFR INTO
@tblName, @colName
END

close CLOSE curFR
DEALLOCATE curFR"

dsdeming

479 Posts

Posted - 2002-04-11 : 12:52:44

Is this part of a one-time cleanup or a process that will be run frequently? If it's a recurring process, I'd take a look at whatever front-end or batch load populates the database and weed out the bogus characters there. Once the data's clean, you could use check constraints to enforce quality. If it's a one-time thing, I'd try to predefine the actions to be taken when these characters are found ( replace with empty string, set to null, whatever ) and just run the updates in place, as long as your backups are good.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources