Search for special characters in records in database

Question

I have a database in SQL Server with 100 tables inside it.

I need to write a query that parses all the rows in all the columns in all 100 tables and returns the rows that have the special characters %,#.

How do I write a query that parses all the rows in all the tables?

Have a look at http://stackoverflow.com/questions/12824577/how-to-search-all-text-fields-in-a-db-for-some-substring-with-t-sql . You also would want to escape the % character. eg. where col like '%\%%' escape '\' — Daniel PP Cabral, Dec 03 '12 at 10:05

score 1 · Answer 1 · answered Jan 25 '13 at 14:10

Maybe not so subtle solution, but is functional:

USE TSQL2012
GO

DECLARE @ColumnName VARCHAR (50)
DECLARE @TableName VARCHAR (50)
DECLARE @SchemaName VARCHAR (50)
DECLARE @SQLQuery NVARCHAR (200)

DECLARE findSpecialCharacters CURSOR
FOR 
    SELECT c.name, o.name, s.name from sys.columns c
        INNER JOIN sys.objects o ON c.object_id = o.object_id
        INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    WHERE o.type = 'U'
OPEN findSpecialCharacters
FETCH NEXT FROM findSpecialCharacters
INTO @ColumnName, @TableName, @SchemaName
WHILE @@FETCH_STATUS = 0    
BEGIN
    SET @SQLQuery = 
        'SELECT ' + @ColumnName + ', * FROM ' + 
            @SchemaName + '.' + @TableName + 
        ' WHERE (' + @ColumnName + ' LIKE ' + 
            CHAR(39) + CHAR(37) + '[,]' + CHAR(37) + CHAR(39) + ') OR (' + 
         @ColumnName + ' LIKE ' + 
            CHAR(39) + CHAR(37) + '[#]' + CHAR(37) + CHAR(39) + ') OR (' + 
         @ColumnName + ' LIKE ' + 
            CHAR(39) + CHAR(37) + '[%]' + CHAR(37) + CHAR(39) + ')'

    PRINT 'Table: ' + @TableName + '; Column: ' + @ColumnName
    PRINT @SQLQuery
    EXEC sp_executesql @SQLQuery



    FETCH NEXT FROM findSpecialCharacters
    INTO @ColumnName, @TableName, @SchemaName
END
CLOSE findSpecialCharacters
DEALLOCATE findSpecialCharacters

First, I was searching for all columns in all tables, and that result set put in FOR SELECT cursor statement. If table has five columns, then my cursor will create five different result set, depending on which column is WHERE filter.

But, for distinction on which column is searching, I simple put that column as first in select list.

Search for special characters in records in database

1 Answers1