sp_searchit

USE master
GO

ALTER PROCEDURE [dbo].[sp_searchit] ( @val varchar(255), @escape char(1) = NULL, @table sysname = NULL )
AS
BEGIN
   SET NOCOUNT ON

   DECLARE @cols TABLE ( id int identity(1,1), colname sysname, tabname sysname )
   DECLARE @results TABLE ( id int identity(1,1), colval varchar(max), colname sysname, tabname sysname )

   DECLARE @id int
         , @colname sysname
         , @tabname sysname
         , @sql nvarchar(4000)

     INSERT INTO @cols ( colname, tabname )
          SELECT A.name "colname"
               , B.name "tabname"
            FROM syscolumns A (NOLOCK)
      INNER JOIN sysobjects B (NOLOCK)
              ON A.id = B.id
           WHERE A.type IN ( SELECT B.type FROM systypes B (NOLOCK) WHERE B.name IN ( 'char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext', 'sysname' ) )
             AND B.type = 'U'
             AND ( @table IS NULL OR B.name = @table )

          SELECT @id = max(id) 
            FROM @cols

   WHILE @id > 0
    BEGIN
      SELECT @colname = A.colname
           , @tabname = A.tabname
        FROM @cols A
       WHERE A.id = @id

      SET @sql = 'SELECT TOP 1 [' + @colname + '] "result", ''' + @colname + ''' "column", ''' + @tabname + ''' "table" FROM [' + @tabname + '] WHERE [' + @colname + '] LIKE ''%' + @val + '%'''
      IF @escape IS NOT NULL
       BEGIN
         SET @sql = @sql + ' ESCAPE ''' + @escape + ''''
       END

      INSERT INTO @results ( colval, colname, tabname )
      EXEC sp_executesql @sql

      SET @id = @id - 1
    END

          SELECT * 
            FROM @results
END --sp_searchit

GO