Blog Post

Brute-force searching for data.

,

Today, I needed a way of searching through a database, any database, looking for a certain string. The procedure I had was an old one that used the information_schema and I couldn't figure out how to update it to take in user-types, so I wrote one using system views. It lists the table, the column and the number of rows that contained the string.  This is the 'brute-force' search, where one just finds every possible column that could contain a string and searching it with a LIKE expression. This is something that one hopes is never necessary in a 'well-ordered universe' but I occasionally need to do it just to find where a certain string is being held. It is slow. Even on Adventureworks, it takes a minute on my test machine for the first search (far quicker with subsequent searches). On the live database that I was examining (not one of mine, I hasten to add) it took three minutes, but it was a lot faster than any other way I could think of. I've left it as a batch rather than procedure in the hope that someone else will find it useful. This particular version doesn't look at CLR types but then I knew the string wasn't stored in a CLR type!

In the example, the @Searchterm is set to look for the tell-tale HREF= of an anchor. You'll need to put all the wildcard characters in when you change it.

DECLARE @command NVARCHAR(MAX) --the SQL Statement that will be executed
DECLARE @SearchTerm NVARCHAR(100) --the term to be searched
SELECT @SearchTerm='%href%'
--start by assembling UNION of all rows in each table that can be checked
SELECT  @command = COALESCE(@command + '
UNION ALL '
, '')--use fact of null in variable to omit first UNION ALL
        
+ 'SELECT '
        
+ ''''+SCHEMA_NAME(t.schema_id) + '.'
              
+ QUOTENAME(REPLACE(t.NAME,'''',''''''))+''''--table name
        
+ ' AS tableName, COUNT(*) AS numberOfRows, '''
        
+ QUOTENAME(REPLACE(c.NAME,'''','''''')) + ''' AS columnName
        FROM '
+ SCHEMA_NAME(t.schema_id) + '.'
              
+ QUOTENAME(REPLACE(t.NAME,'''',''''''))--table name
        
+' WHERE '
        
+ CASE WHEN (ty.name IN ( 'text', 'ntext', 'xml', 'sql_variant')
                      OR
is_user_defined=1)                    
                  
THEN 'CONVERT(nvarchar(max), '
                        
+ QUOTENAME(REPLACE(c.NAME,'''','''''')) + ')'
              
WHEN ty.name IN ( 'char', 'nchar', 'varchar','nvarchar')  
                  
THEN QUOTENAME(REPLACE(c.NAME,'''',''''''))
              
ELSE '''''' --this must never happen!
          
END
      
+ ' LIKE '''+REPLACE(@searchTerm,'''','''''')+''' '                

FROM   sys.columns AS c --we search all possible columns
      
INNER JOIN sys.types AS ty --of  particular data types
                
ON c.user_type_id = ty.user_type_id
      
INNER JOIN   sys.tables AS t --with their tables
                
ON c.OBJECT_ID = t.OBJECT_ID
      
WHERE (ty.name IN ('char','nchar','nvarchar','varchar','text',
                                      
'ntext', 'xml', 'sql_variant')
       OR  
is_user_defined=1 )      
SELECT  @command='
SELECT * from ('
+@command+') hits
WHERE hits.numberOfRows > 0'
EXECUTE (@command)
                    

Rate

Share

Share

Rate