I'd seen a similar requirement before, so I created this procedure...you have to pass it two parameters: the tablename and the search term.

it uses dynamic sql to quiery just the char/varchar type columns for the search term:

ALTER PROCEDURE TABLEVIEWSEARCH @TABLENAME VARCHAR(60),@SEARCHSTRING VARCHAR(50)

-- EXEC TABLEVIEWSEARCH 'GMACT','demo'

-- EXEC TABLEVIEWSEARCH 'TABLEORVIEW','TEST'

AS

SET NOCOUNT ON

DECLARE @SQL VARCHAR(500),

@COLUMNNAME VARCHAR(60)

CREATE TABLE #RESULTS(TBLNAME VARCHAR(60),COLNAME VARCHAR(60),SQL VARCHAR(600))

SELECT

SYSOBJECTS.NAME AS TBLNAME,

SYSCOLUMNS.NAME AS COLNAME,

TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE

INTO #TMPCOLLECTION

FROM SYSOBJECTS

INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID

WHERE SYSOBJECTS.NAME = @TABLENAME

AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')

ORDER BY TBLNAME,COLNAME

DECLARE C1 CURSOR FOR

SELECT COLNAME FROM #TMPCOLLECTION ORDER BY COLNAME

OPEN C1

FETCH NEXT FROM C1 INTO @COLUMNNAME

WHILE @@FETCH_STATUS -1

BEGIN

--SET @SQL = 'SELECT ''' + @TABLENAME + ''' AS TABLENAME,''' + @COLUMNNAME + ''' AS COLUMNNAME,* FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''%' + @SEARCHSTRING + '%'''

SET @SQL = 'IF EXISTS(SELECT * FROM [' + @TABLENAME + '] WHERE [' + @COLUMNNAME + '] LIKE ''%' + @SEARCHSTRING + '%'') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''' + @COLUMNNAME + ''','' SELECT * FROM [' + @TABLENAME + '] WHERE [' + @COLUMNNAME + '] LIKE ''''%' + @SEARCHSTRING + '%'''''') ;'

PRINT @SQL

EXEC (@SQL)

FETCH NEXT FROM C1 INTO @COLUMNNAME

END

CLOSE C1

DEALLOCATE C1

SELECT * FROM #RESULTS

GO

ALTER PROCEDURE TABLEVIEWSEARCH2 @TABLENAME VARCHAR(60),@SEARCHSTRING VARCHAR(50)

-- EXEC TABLEVIEWSEARCH2 'GMACT','SOURCE'

-- EXEC TABLEVIEWSEARCH2 'TABLEORVIEW','TEST'

AS

BEGIN

SET NOCOUNT ON

DECLARE @FINALSQL VARCHAR(MAX),

@COLUMNNAMES VARCHAR(MAX)

SET @FINALSQL = 'SELECT * FROM [' + @TABLENAME + '] WHERE 1 = 2 '

SELECT

@FINALSQL = @FINALSQL + ' OR [' + SYSCOLUMNS.NAME + '] LIKE ''%' + @SEARCHSTRING + '%'' '

FROM SYSCOLUMNS

WHERE OBJECT_NAME(id) = @TABLENAME

AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')

ORDER BY COLID

PRINT @FINALSQL

EXEC(@FINALSQL)

END --PROC