One good idea is only search from columns which column length is

greather than @searchValue length

set nocount on

--initial declarations

declare @rowID INT, @maxRowID INT

declare @sql NVARCHAR(4000)

declare @statements TABLE (rowID INT, SQLL NVARCHAR(MAX) COLLATE DATABASE_DEFAULT)

CREATE TABLE #results (tableName NVARCHAR(250) COLLATE DATABASE_DEFAULT, tableSchema NVARCHAR(250) COLLATE DATABASE_DEFAULT

, columnName NVARCHAR(250) COLLATE DATABASE_DEFAULT, foundtext NVARCHAR(MAX) COLLATE DATABASE_DEFAULT )

SET @rowID = 1

declare @searchValue NVARCHAR(100)

SET @searchValue = 'text to find'

DECLARE @pikkus INT

SET @pikkus=LEN(@searchValue)

--TEXT 35

--NTEXT 99

--VARCHAR 167

--CHAR 175

--NVARCHAR, SYSNAME 231

--NCHAR 239

--XML 241

--create CTE table holding metadata

;WITH MyInfo (tableName, tableSchema, columnName, XTYPE) AS (

SELECT sysobjects.name AS tableName, USER_NAME(sysobjects.uid) AS tableSchema

, syscolumns.name AS columnName, syscolumns.XTYPE

FROM sysobjects WITH(NOLOCK) INNER JOIN syscolumns WITH(NOLOCK)

ON (sysobjects.id = syscolumns.id)

WHERE sysobjects.xtype = 'U' AND sysobjects.category=0

AND sysobjects.name <> 'sysdiagrams' --MSSQL diagrams

AND syscolumns.XTYPE IN (35,99,167,175,231,239,214) AND syscolumns.prec >= @pikkus

)

--create search strings

INSERT INTO @statements

SELECT row_number() over (order by tableName, columnName) AS rowID, 'INSERT INTO #results SELECT '''+tableName+''', '''+tableSchema+''', '''+columnName+''', CAST('+columnName+' AS NVARCHAR(MAX)) FROM ['+tableSchema+'].['+tableName+'] WITH (NOLOCK) WHERE '+

CASE WHEN myInfo.XTYPE=241 --XML

THEN +'CONVERT(NVARCHAR(MAX),['+columnName+'])'

ELSE '['+columnName+']'

END+' LIKE ''%'+@searchValue+'%'''

FROM myInfo

--select * from @statements

--initialize while components and process search strings

SET @maxRowID = ( SELECT MAX(rowID) FROM @statements )

WHILE @rowID <= @maxRowID

BEGIN

SET @sql = (SELECT sqll FROM @statements WHERE rowID = @rowID )

EXEC sp_executeSQL @sql

SET @rowID = @rowID + 1

END

--view results and cleanup

SELECT * FROM #results

drop table #results