Full String Search
This script will search a specified string in the current database and return all the tables with their columns containing that string.
SET @search_str variable with the string to be searched (max. length allowed = 1000 chrs)
DECLARE
@VAR1 SYSNAME,
@VAR2 SYSNAME,
@VAR3 NVARCHAR(4000),
@search_str NVARCHAR(1000),
@counter INT,
@dbname SYSNAME
-- SET SEARCH CONDITION HERE--
SET @search_str='my search string'
IF EXISTS (SELECT 1 FROM TEMPDB..SYSOBJECTS WHERE NAME LIKE '#FTS_TABLE%')
DROP TABLE #FTS_TABLE
CREATE TABLE #FTS_TABLE (TABLE_NAME VARCHAR(150), CONTENT VARCHAR(1000),
CONTAIN VARCHAR(15), SEARCH_STRING VARCHAR(1000))
SET @counter=0
SELECT @dbname= db_name()
DECLARE CUR CURSOR FOR
SELECT SYSOBJECTS.NAME , SYSCOLUMNS.NAME FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID = SYSCOLUMNS.ID
INNER JOIN SYSTYPES ON SYSCOLUMNS.XTYPE = SYSTYPES.XTYPE WHERE
SYSOBJECTS.TYPE ='U' AND
OBJECTPROPERTY(OBJECT_ID(SYSOBJECTS.NAME), N'ISMSSHIPPED') = 0 AND
SYSTYPES.NAME IN('CHAR', 'VARCHAR','TEXT')--AND
--SYSOBJECTS.NAME LIKE '%MAM_%'
ORDER BY SYSOBJECTS.NAME, SYSCOLUMNS.COLID
OPEN CUR
FETCH NEXT FROM CUR INTO @VAR1, @VAR2
WHILE @@FETCH_STATUS = 0
BEGIN
SET @VAR3=N'IF EXISTS( SELECT 1 FROM '+@VAR1+' WHERE '+@VAR2+' LIKE '''+
'%'+@search_str+'%'+''' )INSERT INTO #FTS_TABLE SELECT '
+''''+UPPER(@VAR1+'('+''+@VAR2)+')'+' HAS STRING '''+' ,'+
@VAR2+','+'''CONTAINING '''+','''+UPPER(@search_str)+''' FROM '+@VAR1+
' WHERE '+@VAR2+' LIKE '+''''+'%'+@search_str+'%'+''''
EXEC SP_EXECUTESQL @VAR3
IF @@ROWCOUNT>0
set @counter=@counter+1
FETCH NEXT FROM CUR INTO @VAR1, @VAR2
END
CLOSE CUR
DEALLOCATE CUR
IF @counter=0
BEGIN
IF CHARINDEX('-',HOST_NAME())<>0
PRINT 'Sorry '+REPLACE(HOST_NAME(),SUBSTRING(HOST_NAME(), CHARINDEX('-',HOST_NAME()), 12),'')+
', no records matching your search string '''+upper(@search_str)+''''+CHAR(13)+'found in database '+
@dbname+CHAR(13)+'Please try again with other strings.'
ELSE
PRINT 'Sorry '+HOST_NAME()+
', no records matching your search string '''+UPPER(@search_str)+''''+CHAR(13)+'found in database '+
@dbname+CHAR(13)+'Please try again with other strings.'
END
ELSE
SELECT TABLE_NAME 'TABLE(COLUMN)', CONTENT 'VALUE', CONTAIN ' ',
SEARCH_STRING 'YOUR SEARCH STRING'
FROM #FTS_TABLE WHERE TABLE_NAME NOT LIKE '%#FTS_TABLE%' 
