-- CREATE TABLE myTable99 (  TABLE_SCHEMA sysname
-- 			, TABLE_NAME sysname
-- 			, COLUMN_NAME sysname
-- 			, ORDINAL_POSITION int
-- 			, Occurs int
-- 			, SysTime datetime
-- 			, SearchString varchar(80)
--			, RowNum int IDENTITY(1,1))
-- GO


SET NOCOUNT ON

DECLARE   @SQL varchar(8000), @Sargable varchar(80), @Count int
	, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAME sysname, @ORDINAL_POSITION int

SELECT @Sargable = 'PruSelect'

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



OPEN insaneCursor
 
FETCH NEXT FROM insaneCursor INTO @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @ORDINAL_POSITION

WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @SQL = 'INSERT INTO myTable99 (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION'
				+',Occurs, SysTime, SearchString) SELECT '
				+ '''' +  @TABLE_SCHEMA + '''' + ','
				+ '''' +  @TABLE_NAME + '''' + ','
				+ '''' + @COLUMN_NAME + '''' + ','
				+ CONVERT(varchar(3),@ORDINAL_POSITION) + ','
				+ 'COUNT(*)'
				+ ',GetDate()'
				+ ','+''''+ @Sargable + ''''
				+ ' FROM [' + @TABLE_NAME  
				+ '] WHERE [' + @COLUMN_NAME + '] Like '
				+ ''''+ '%' + @Sargable + '%' + ''''
		--SELECT @SQL
		EXEC(@SQL)
		IF @@ERROR <> 0 
			BEGIN 
				SELECT @SQL
				SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @TABLE_NAME
				GOTO Error
			END 
		FETCH NEXT FROM insaneCursor  INTO @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @ORDINAL_POSITION
	END

SELECT * FROM myTable99 WHERE Occurs <> 0 


Error:
CLOSE insaneCursor
DEALLOCATE insaneCursor

GO

--DROP TABLE myTable99
--GO