Full database search for text


This one was found by Dave Kay, a co-worker.  It has come in handy already and I’m sure I will use it again.

Warning: it can be a pig – it is the kind of query you want to run on a mirror database… overnight… on a weekend… preferably a long weekend… from someone else’s workstation… while wearing gloves and a ski mask.

DECLARE @SearchStr NVARCHAR(100)

SET @SearchStr = 'search'

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT
DECLARE @Results TABLE
  (
     columnname  NVARCHAR(370),
     columnvalue NVARCHAR(3630)
  )

SET nocount ON

DECLARE @TableName  NVARCHAR(256),
        @ColumnName NVARCHAR(128),
        @SearchStr2 NVARCHAR(110)

SET @TableName = ''
SET @SearchStr2 = Quotename('%' + @SearchStr + '%', '''')

WHILE @TableName IS NOT NULL
  BEGIN
      SET @ColumnName = ''
      SET @TableName = (SELECT Min(Quotename(table_schema) + '.' + Quotename(table_name))
                        FROM   information_schema.tables
                        WHERE  table_type = 'BASE TABLE'
                               AND Quotename(table_schema) + '.' + Quotename(table_name) > @TableName
                               AND Objectproperty(Object_id(Quotename(table_schema) + '.' + Quotename(table_name)), 'IsMSShipped') = 0)

      WHILE ( @TableName IS NOT NULL )
            AND ( @ColumnName IS NOT NULL )
        BEGIN
            SET @ColumnName = (SELECT Min(Quotename(column_name))
                               FROM   information_schema.columns
                               WHERE  table_schema = Parsename(@TableName, 2)
                                      AND table_name = Parsename(@TableName, 1)
                                      AND data_type IN ( 'char', 'varchar', 'nchar', 'nvarchar' )
                                      AND Quotename(column_name) > @ColumnName)

            IF @ColumnName IS NOT NULL
			  BEGIN
				INSERT INTO @Results
				EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) ' +
					'FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 )
			  END
		END
  END

SELECT columnname,
       columnvalue
FROM   @Results  
Advertisements

4 thoughts on “Full database search for text”

  1. Wow, what would you ever need to use a full database search for? Pretty interesting though.

    Databases tend to be incredibly slow at doing any string manipulation. Wouldn’t it be easier to pull all the data into C#, PHP, whatever and then parse the text from there? Heck, you don’t even need the database at all, you could back the entire database up as an SQL file, and then scan it using your code from there.

  2. In this case we were searching for the occurrence of a particular e-mail address. We looked in all expected locations with no luck. So we tried using this to see if it was somewhere in the database that we hadn’t thought of.
    Brute force may be ugly, but sometimes it is the most effective – the Mongols didn’t have shiny armour or pretty castles, but they still carved out the largest empire the world had ever seen.

  3. Did you end up finding the email address you were looking for?

    Databases are fast when handling data relationships, but if you’re doing a string comparison search through ALL of the data, I assume that it’s incredibly inefficient.

    Like I mentioned above, using C# to scan through an SQL data dump of the database would probably be faster.

    1. No, I didn’t find the email address. I suspect that the address in question was actually in one of the flat files and not in the db after all.

      You’re right: an scan of a data dump would probably be faster, but may not be as convenient. Sometimes Quick and Dirty is easier, even if it is Slow and Dirty.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s