SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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 + '
, '')--use fact of null in variable to omit first UNION ALL
+ ''''+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')
THEN 'CONVERT(nvarchar(max), '
+ QUOTENAME(REPLACE(c.NAME,'''','''''')) + ')'
WHEN ty.name IN ( 'char', 'nchar', 'varchar','nvarchar')  
ELSE '''''' --this must never happen!
+ ' 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
WHERE (ty.name IN ('char','nchar','nvarchar','varchar','text',
'ntext', 'xml', 'sql_variant')
is_user_defined=1 )      
SELECT  @command='
SELECT * from ('
+@command+') hits
WHERE hits.numberOfRows > 0'
EXECUTE (@command)


Posted by lanex.z on 12 July 2011

It's great!!!

Posted by dappling on 15 July 2011

tried this out on one our test databases. got the following error:Too many table names in the query. The maximum allowable is 256. I consider myself an SQL Server user and not a DBA. So is this a problem with the database just being to large in regards to the number of tables or a limitation of sql server than can be changed?

Posted by Phil Factor on 15 July 2011

I can fix this, but it would be a much more complex query. Probably best to do a separate query for big databases. The problem is that the query creates one large query to scan all the tables to get the best performance. It maxes out on 256 tables. The fix would be to collect them into a temporary table in 256-table gulps.

Posted by rwhite-957607 on 15 July 2011

This is so ironic.  I just had to do this same thing yesterday, unfortunately.  I was trying to find something in the MS TFS Database.  Another story!  Anyway, here was my very similar, not very elegant, but very successful, way that gets around the 256 problem with the union (but has other minor issues).  I have this setup as a template (Template Explorer) called 'SearchForTextInAnyColumn'.  If you aren't using Templates, check them out...they're neat.

BTW - TFS links are stored in a table called WorkItemFiles.  There is a column called FilePath.  Also, the field we had to update had become Fld10096 in tables called WorkItemsAre, WorkItemsWere, and WorkItemsLatest.  

Very creative solution Phil to use the union.







   row_number() over (order by column_name) as row_no





declare @row_index      int

declare @col_count      int

declare @table_name     sysname

declare @column_name    sysname

declare @sql            nvarchar(max)

declare @search_text    varchar(100)

set     @row_index      = 1

set     @search_text    = '<search_text, varchar(100),>'


   @col_count = count(*)



while (@row_index <= @col_count)



       @table_name     = table_name,

       @column_name    = column_name




       (row_no = @row_index)

   set @sql = ' select ' + @column_name +

              ' from ' + @table_name +

              ' where convert(varchar(max), ' + @column_name + ') ' +

              ' like ' + '''' + '%' + @search_text + '%' + ''''

   print @sql        

   exec sp_executesql @sql                

   set @row_index = @row_index + 1


drop table #col_list    

Posted by Eric Russell on 15 July 2011

I've used this thing in the past. What it does is return a list of SQL statements, which can then be executed as a batch or individually as needed. It returns SQL selects only for tables (redundant to do the same on views), and it also only returns a SQL select only for those columns where character_maximum_length >= len(@search_string), to prevent wasted processing.

declare @search_string varchar(8000); select @search_string = 'Johnson';


 'select '''

 +c.table_schema+'.'+c.table_name+''' as table_name, '

 +''''+c.column_name+''' as column_name, '

 +'count(*) row_count from '

 +c.table_schema+'.'+c.table_name+' where '

 +c.column_name+' like ''%'+@search_string+'%'';'

from information_schema.columns c

 join information_schema.tables t on t.table_name = c.table_name

   and table_type = 'base table'

where c.data_type in ('char','varchar')

 and c.character_maximum_length >= len(@search_string);

Posted by gary.rumble on 15 July 2011

I use Red Gate's SQL Search


It's a free SSMS plugin, but I don't know if meets your requirements here.

Posted by Entz on 15 July 2011

Or use Find it EZ


Leave a Comment

Please register or log in to leave a comment.