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.

Solution 1

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

Solution 2

You can use in:

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

Solution 3

You can use in and you can get the column names dynamically and pass them to IN clause by making sql string and executing it using execute sp_executesql.

declare @sql nvarchar(2100)
declare @cols nvarchar(2000)
declare @toSearch nvarchar(200)
declare @tableName nvarchar(200)
set @tableName = 'tbltemp'
set @toSearch = '5' 
set @cols =(
 SELECT LEFT(column_name, LEN(column_name) - 1)
FROM (
    SELECT column_name + ', '
    FROM INFORMATION_SCHEMA.COLUMNS where table_name = @tableName
    FOR XML PATH ('')
  ) c (column_name )
)
set @sql = 'select * from tbltemp where '''+ @toSearch + '''  in (' + @cols + ')';  
execute sp_executesql @sql

Solution 4

I think this is one of the best ways of doing it

SELECT * FROM sys.columns  a
inner join 
(
SELECT object_id
FROM sys.tables 
where 
type='U'--user table
and name like 'testing' 
) b on a.object_id=b.object_id
WHERE a.name like '%foo%'

View more solutions

Comments

[nsilva]

Thanks, is it possible without defining every column name? @Gordon Linoff

[Gordon Linoff]

@nsilva . . . Yes, but you wouldn't want to do that. One method I can think of would involve a rather complicated XML trick. You can get the list of columns from information_schema.columns if you are averse to typing them.

[Ataboy Josef]

Yes @Gordon! 'SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'testing';' -will give you all table values together.

[ubaid ashraf]

I tested it and it works, just give it a run @nsilva

[nsilva]

Hi, I've just tried, I did a SELECT * FROM testing to find a value I want to return to check, but running the script just returned nothing. I've changed table name, and SELECT at the bottom to match the database i'm checking, there's nothing else I need to change right?

[ubaid ashraf]

you just need to keep in mind that schemna name in this case dbo and table name are given correctly. I am giving table and schemna at start of procedure. Alternatively you can try 2nd method

[ubaid ashraf]

If you go for first method, you need to create stored procedure!! @nsilva But second method it too easy, just check that also

[Remus Rusanu]

Object names are Unicode, use NVARCHAR. Do not write in-house quoting a-la '['+ C.name +']' , use QUOTENAME, you are not handling properly escape sequences in quoted identifiers. Always quote a name, not only if it contains space, there are many more problem chars (\n, \t obvious ones, but there a whole lot more in Unicode space).

[BornToCode]

This doesn't work if you have int columns in your table.

[aelveborn]

Apart from issues mentioned by @RemusRusanu, the "first method" does not work in principle. It (badly) generates a string that contains comma-separated column names and uses that string in the IN clause which makes no sense and is equivalent to WHERE 'foo' = 'column1,column2,column3', which is not going to find anything. It does make sense to build comma-separated list of columns, but it then must be used to build dynamic SQL.

[Eray Balkanli]

did not work for me. it was like --> ... where 1 in (calculation1, calculation2)

[BornToCode]

@ErayBalkanli - I just verified again and it does work. I don't understand from your comment what it did not work. If you could make it more clear I can try to help..

[Gordon Linoff]

@eMi . . . That is a rather generic statement to make about working code. You could just try it out.

[eMi]

@GordonLinoff it works if you search for the exact word "foo" but it doesn't work in a way with LIKE "%foo%". You should add that variant too. It is different and not possible with the IN.

[eMi]

SELECT * FROM testing WHERE col1 LIKE '%foo%' OR col2 LIKE '%foo%' ...

[thomasrutter]

Oh. My. Goodness. I have been using MySQL for something like 20 years and had never realised that IN() can be used this way around. I feel silly. This has really simplified a query I just had to make. Thank you!!!

[EJoshuaS - Stand with Ukraine]

This should be the accepted answer - it's by far the simplest way of doing this.