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