pls try this
replace the 'yourCheckString' with the substring you are searching in the tables, here this script will check all the tables for the substring and retruns the column count which contains the matched string.
DECLARE @table VARCHAR(64)
DECLARE @column VARCHAR(64)
DECLARE @BuildCondition VARCHAR(200)
DECLARE @BuildSpecialChars VARCHAR(200)
DECLARE @CheckSubstring VARCHAR(200)
DECLARE @sql VARCHAR(500)
SET @CheckSubstring='yourCheckString';
SET @BuildSpecialChars ='PATINDEX(''%'+@CheckSubstring+'%'',[somecolumn])>0'
if OBJECT_ID('tempdb..#t')is not null
Drop table #t
CREATE TABLE #t (
tablename VARCHAR(64),
columnname VARCHAR(64),
specialchar nvarchar(64)
)
DECLARE TempTables CURSOR
FOR
SELECT o.name, c.name
FROM syscolumns c
INNER JOIN sysobjects o ON c.id = o.id
WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239)
ORDER BY o.name, c.name
OPEN TempTables
FETCH NEXT FROM TempTables
INTO @table, @column
WHILE @@FETCH_STATUS = 0
BEGIN
SET @BuildCondition='';
SET @BuildCondition=REPLACE(@BuildSpecialChars,'somecolumn',@column);
SET @sql = 'IF EXISTS(SELECT NULL FROM ' + @table + ' '
SET @sql = @sql + 'WHERE '+@BuildCondition+') '
SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', '''
SET @sql = @sql + @column + ''','''
SET @sql = @sql + ''')'
EXEC(@sql)
FETCH NEXT FROM TempTables
INTO @table, @column
END
CLOSE TempTables
DEALLOCATE TempTables
SELECT count(*) FROM #t
hope this will help...