MSSQLTips

I'm sure there is an easy way to do this but I'd like to know if anyone knows how to query an entire base throughout ALL the columns in the table for a specific search string.  For example: if you type "foo" in Google you'll get a zillion hits of every occurance of the word foo.  I'd like to do the same thing on my SQL db and check EACH column to see if it contains "foo".

 select * from myTable where lastName="foo" is fine for just checking last names but I'd like to check the entire table.

 Any cool SQL way to do this?

thanks!

-Paul

There is not a simple way to search across all columns without building the query to look at each column.

Here is a script that allows you to use the Information Schema data to build the query for you.

DECLARE @sqlCommand varchar(1000)
DECLARE @stringToFind varchar(100)
DECLARE @tableName sysname
DECLARE @schemaName sysname
DECLARE @databaseName sysname
DECLARE @where varchar(1000)
DECLARE @column_name sysname

SET @databaseName = 'AdventureWorks'
SET @schemaName = 'Person'
SET @tableName = 'Address'
SET @stringToFind = '''%New%'''
SET @sqlCommand = 'SELECT * FROM ' + @databaseName + '.' + @schemaName + '.' + @tableName + ' WHERE'
SET @where = ''

DECLARE col_cursor CURSOR FOR 
 SELECT COLUMN_NAME
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_CATALOG = @databaseName
  AND TABLE_SCHEMA = @schemaName
  AND TABLE_NAME = @tableName
  AND DATA_TYPE NOT IN ('uniqueidentifier', 'xml')

OPEN col_cursor  
FETCH NEXT FROM col_cursor INTO @column_name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
 IF @where <> ''
  SET @where = @where + ' OR'

 SET @where = @where + ' ' + @column_name + ' LIKE ' + @stringToFind
 FETCH NEXT FROM col_cursor INTO @column_name  
END  

CLOSE col_cursor  
DEALLOCATE col_cursor

SET @sqlCommand = @sqlCommand + @where
--PRINT @sqlCommand
EXEC (@sqlCommand) 

Thank you for the code example - I'll give it a shot!  Another thought was to use Index Server for the look up -- any thoughts on that?