SQL Server SELECT where any column contains 'x'

Question

Using SQL Server 2008, say I have a table called testing with 80 columns and I want to find a value called foo.

I can do:

SELECT *  FROM testing  WHERE COLNAME = 'foo' 

Is it possible I can query all 80 columns and return all the results where foo is contained in any of the 80 columns?

Thanks in advance.

Answer 1

You can use in:

SELECT * FROM testing  WHERE 'foo' in (col1, col2, col3, . . . ); 

Answer 2

First Method(Tested) First get list of columns in string variable separated by commas and then you can search 'foo' using that variable by use of IN

Check stored procedure below which first gets columns and then searches for string:

DECLARE @TABLE_NAME VARCHAR(128) DECLARE @SCHEMA_NAME VARCHAR(128)  -----------------------------------------------------------------------  -- Set up the name of the table here : SET @TABLE_NAME = 'testing' -- Set up the name of the schema here, or just leave set to 'dbo' : SET @SCHEMA_NAME = 'dbo'  -----------------------------------------------------------------------  DECLARE @vvc_ColumnName VARCHAR(128) DECLARE @vvc_ColumnList VARCHAR(MAX)  IF @SCHEMA_NAME =''   BEGIN   PRINT 'Error : No schema defined!'   RETURN   END  IF NOT EXISTS (SELECT * FROM sys.tables T JOIN sys.schemas S       ON T.schema_id=S.schema_id       WHERE T.Name=@TABLE_NAME AND S.name=@SCHEMA_NAME)   BEGIN   PRINT 'Error : The table '''+@TABLE_NAME+''' in schema '''+       @SCHEMA_NAME+''' does not exist in this database!'    RETURN  END  DECLARE TableCursor CURSOR FAST_FORWARD FOR SELECT   CASE WHEN PATINDEX('% %',C.name) > 0       THEN '['+ C.name +']'       ELSE C.name       END FROM     sys.columns C JOIN     sys.tables T ON       C.object_id  = T.object_id JOIN     sys.schemas S ON       S.schema_id  = T.schema_id WHERE    T.name    = @TABLE_NAME AND      S.name    = @SCHEMA_NAME ORDER BY column_id   SET @vvc_ColumnList=''  OPEN TableCursor FETCH NEXT FROM TableCursor INTO @vvc_ColumnName  WHILE @@FETCH_STATUS=0   BEGIN    SET @vvc_ColumnList = @vvc_ColumnList + @vvc_ColumnName      -- get the details of the next column    FETCH NEXT FROM TableCursor INTO @vvc_ColumnName    -- add a comma if we are not at the end of the row    IF @@FETCH_STATUS=0     SET @vvc_ColumnList = @vvc_ColumnList + ','    END   CLOSE TableCursor  DEALLOCATE TableCursor  -- Now search for `foo`   SELECT * FROM testing  WHERE 'foo' in (@vvc_ColumnList ); 

2nd Method In sql server you can get object id of table then using that object id you can fetch columns. In that case it will be as below:

Step 1: First get Object Id of table

select * from sys.tables order by name     

Step 2: Now get columns of your table and search in it:

 select * from testing where 'foo' in (select name from sys.columns  where  object_id =1977058079) 

Note: object_id is what you get fetch in first step for you relevant table