OK, so I got tired of writing this...so I decided to build a bells and whistles full database search.  Now at first blush, the reaction that this is bad....and it is...but I've been handed so many poorly designed databases, I felt for this persons post.  So now I'm done...I don't have to write it again....I guess we could expanded it to server the entire server....

EDIT: Modified to Search Text and ntext as well.  Changes are highlighted in Red.  Anyone know why the Row counts are still being produced?

USE Northwind
GO

SET NOCOUNT OFF
CREATE TABLE myTableSearch99(
   SPID int
 , SearchDate datetime
 , TABLE_CATALOG sysname
 , TABLE_SCHEMA sysname
 , TABLE_NAME sysname
 , COLUMN_NAME varchar(255)
 , DATA_TYPE varchar(25)
 , SEARCHARG varchar(2000)
 , RESULTS varchar(4000))
GO

CREATE PROC myDBSearch99
 @SearchArg varchar(2000)
AS
  BEGIN
 SET NOCOUNT ON
 DECLARE @sql varchar(8000), @Date datetime
 DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAME sysname, @DATA_TYPE varchar(25)
 SELECT @date = GetDate() 

 DELETE FROM  myTableSearch99 WHERE SPID = @@SPID

 DECLARE myCursor99 CURSOR FOR
  SELECT c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME , c.DATA_TYPE
    FROM INFORMATION_SCHEMA.Columns c
     INNER JOIN INFORMATION_SCHEMA.Tables t
      ON c.TABLE_CATALOG = t.TABLE_CATALOG
     AND c.TABLE_SCHEMA  = t.TABLE_SCHEMA
     AND c. TABLE_NAME   = t.TABLE_NAME
   WHERE t.TABLE_TYPE = 'BASE TABLE'
     AND c.DATA_TYPE IN ('char','nchar','varchar','nvarchar','text','ntext','datetime')
     AND t.TABLE_NAME <> 'myTableSearch99'

 OPEN myCursor99
 FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE

 INSERT INTO myTableSearch99(SPID,SearchDate,TABLE_CATALOG,TABLE_SCHEMA, TABLE_NAME, SEARCHARG)
 SELECT @@SPID, CONVERT(varchar(25),@Date,120), @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @SearchArg

 WHILE @@FETCH_STATUS = 0
   BEGIN
  SELECT @sql = 'SET NOCOUNT ON ' + CHAR(13)
    +' INSERT INTO myTableSearch99(SPID,SearchDate,TABLE_CATALOG,TABLE_SCHEMA, ' +
    + 'TABLE_NAME,COLUMN_NAME,DATA_TYPE,SEARCHARG,RESULTS) '
    + 'SELECT ' + CONVERT(varchar(15),@@SPID) + ',' + ''''
    + CONVERT(varchar(25),@Date,120) + '''' + ',' + '''' +
    + @TABLE_CATALOG + ''',''' + @TABLE_SCHEMA + ''',''' +  @TABLE_NAME + ''',' + ''''
    + @COLUMN_NAME + ''',' + '''' + @DATA_TYPE + ''',' + ''''+ @SearchArg + '''' + ','
    + 'CONVERT(varchar(4000),'+@COLUMN_NAME+')'
    + ' FROM ' + '['+ @TABLE_CATALOG + '].[' + @TABLE_SCHEMA + '].[' +  @TABLE_NAME + '] '
    + ' WHERE PATINDEX( ' + '''' + '%' + @SearchArg + '%' + '''' + ','
    + CASE WHEN @DATA_TYPE = 'datetime' THEN 'CONVERT(varchar(25),'+@COLUMN_NAME
    + ',121)' ELSE @COLUMN_NAME END
    + ') <> 0'
--    + ' WHERE ' + @COLUMN_NAME + ' LIKE ''%' + @SearchArg + '%'''
--  SELECT @sql
  EXEC(@sql)
  FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE
   END

 SET NOCOUNT OFF

  SELECT   
    SPID
  , SearchDate
  , TABLE_CATALOG
  , TABLE_SCHEMA
  , TABLE_NAME
  , COLUMN_NAME
  , DATA_TYPE
  , SEARCHARG
  , RESULTS
   FROM    myTableSearch99 o
  WHERE SPID = @@SPID
    AND SearchDate = ( SELECT MAX(SearchDate)
    FROM myTableSearch99 i
          WHERE i.SPID = o.SPID)
 CLOSE myCursor99
 DEALLOCATE myCursor99 
  END
GO

EXEC myDBSearch99 'Ale'
GO

EXEC myDBSearch99 'Stout'
GO

EXEC myDBSearch99 '1996-07-04'
GO

SELECT * FROM myTableSearch99 ORDER BY SearchDate DESC, COLUMN_NAME
GO

This was the original

SET NOCOUNT OFF
TRUNCATE TABLE myTableSearch99
DROP TABLE myTableSearch99
DROP PROC myDBSearch99

USE Northwind
GO

SET NOCOUNT OFF
CREATE TABLE myTableSearch99(
   SPID int
 , SearchDate datetime
 , TABLE_CATALOG sysname
 , TABLE_SCHEMA sysname
 , TABLE_NAME sysname
 , COLUMN_NAME varchar(255)
 , SEARCHARG varchar(2000)
 , RESULTS varchar(4000))
GO

CREATE PROC myDBSearch99
 @SearchArg varchar(2000)
AS
  BEGIN
 SET NOCOUNT ON
 DECLARE @sql varchar(8000), @Date datetime
 DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAME sysname
 SELECT @date = GetDate() 

 DECLARE myCursor99 CURSOR FOR
  SELECT c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME
    FROM INFORMATION_SCHEMA.Columns c
     INNER JOIN INFORMATION_SCHEMA.Tables t
      ON c.TABLE_CATALOG = t.TABLE_CATALOG
     AND c.TABLE_SCHEMA  = t.TABLE_SCHEMA
     AND c. TABLE_NAME   = t.TABLE_NAME
   WHERE t.TABLE_TYPE = 'BASE TABLE'
     AND c.DATA_TYPE IN ('char','nchar','varchar','nvarchar','datetime')

 OPEN myCursor99
 FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME

 INSERT INTO myTableSearch99(SPID,SearchDate,TABLE_CATALOG,TABLE_SCHEMA, TABLE_NAME, SEARCHARG)
 SELECT @@SPID, CONVERT(varchar(25),@Date,120), @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @SearchArg

 WHILE @@FETCH_STATUS = 0
   BEGIN
  SELECT @sql = 'INSERT INTO myTableSearch99(SPID,SearchDate,TABLE_CATALOG,TABLE_SCHEMA, ' +
    + 'TABLE_NAME,COLUMN_NAME,SEARCHARG,RESULTS) '
    + 'SELECT ' + CONVERT(varchar(15),@@SPID) + ',' + ''''
    + CONVERT(varchar(25),@Date,120) + '''' + ',' + '''' +
    + @TABLE_CATALOG + ''',''' + @TABLE_SCHEMA + ''',''' +  @TABLE_NAME + ''',' + ''''
    + @COLUMN_NAME + ''',' + ''''+ @SearchArg + '''' + ',' + @COLUMN_NAME
    + ' FROM ' + '['+ @TABLE_CATALOG + '].[' + @TABLE_SCHEMA + '].[' +  @TABLE_NAME + '] '
    + ' WHERE ' + @COLUMN_NAME + ' LIKE ''%' + @SearchArg + '%'''
--  SELECT @sql
  EXEC(@sql)
  FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME
   END

 SET NOCOUNT OFF

  SELECT   
    SPID
  , SearchDate
  , TABLE_CATALOG
  , TABLE_SCHEMA
  , TABLE_NAME
  , COLUMN_NAME
  , SEARCHARG
  , RESULTS
   FROM    myTableSearch99 o
  WHERE SPID = @@SPID
    AND SearchDate = ( SELECT MAX(SearchDate)
    FROM myTableSearch99 i
          WHERE i.SPID = o.SPID)
 CLOSE myCursor99
 DEALLOCATE myCursor99 
  END
GO

EXEC myDBSearch99 'Ale'
GO

EXEC myDBSearch99 'Stout'
GO

SELECT * FROM myTableSearch99 ORDER BY SearchDate DESC, COLUMN_NAME
GO

SET NOCOUNT OFF
TRUNCATE TABLE myTableSearch99
DROP TABLE myTableSearch99
DROP PROC myDBSearch99