How do I find a certain character in any column across an SQL database?

Question

We recently discovered that our data has been corrupted by some editor somewhere (and our backend has not filtered it on the way in... yes, we should fix that). Specifically, we've found a delete character (0x007f) in several places.

So far, we've been able to locate them by finding one when it breaks something, then do SELECT * WHERE [TheColumn] LIKE '%%' (yes, there's a DEL char between the two % signs) on the place where it showed up to see if there are more of them.

Is there a way to do a database-wide query that would show all tables and columns where we need to go through the data and clean it?

In pseudo-code, I'm looking for something equivalent of

SELECT theTable, theColumn
FROM entire database
WHERE for any row in theTable, theColumn matches LIKE '%%' -- DEL char between %'s

Answer 1

You can try this

DECLARE
@search_string  VARCHAR(100),
@table_name     SYSNAME,
@table_id       INT,
@column_name    SYSNAME,
@sql_string     VARCHAR(2000)

SET @search_string = 'StringtoSearch'

DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE  type = 'U'

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id

WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id AND system_type_id IN (167, 175, 231, 239)

OPEN columns_cur

FETCH NEXT FROM columns_cur INTO @column_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE [' + @column_name + '] LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''

    EXECUTE(@sql_string)

    FETCH NEXT FROM columns_cur INTO @column_name
END

CLOSE columns_cur

DEALLOCATE columns_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END

CLOSE tables_cur
DEALLOCATE tables_cur