Find a String in SQL Server

find_a_string.sql

-- Find a string mssql
DECLARE @SQL VARCHAR(MAX) 
DECLARE @valueToFind VARCHAR(100) 
DECLARE @columnName VARCHAR(100) 

SET @valueToFind = '%pete%' 
SET @columnName = '%%' 

CREATE TABLE #TMP 
   (Clmn VARCHAR(500), 
   CNT INT) 

SELECT @SQL=
		COALESCE(@SQL,'')+CAST('INSERT INTO #TMP Select ''' 
		+ TABLE_SCHEMA + '.' 
		+ TABLE_NAME + '.' 
		+ COLUMN_NAME + ''' AS Clmn, count(*) CNT FROM '  
        + TABLE_SCHEMA + '.[' 
		+ TABLE_NAME + '] WHERE [' 
		+ COLUMN_NAME + '] LIKE ''%' 
		+ @valueToFind + '%'' ;'  AS VARCHAR(MAX)) 
FROM INFORMATION_SCHEMA.COLUMNS  
   JOIN sysobjects B  
   ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = B.NAME 
WHERE COLUMN_NAME LIKE @columnName AND xtype = 'U' 
   AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar') 

EXEC(@SQL) 

SELECT * FROM #TMP WHERE CNT > 0 
DROP TABLE #TMP