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