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?

Answer 1

If your table have indexes it can be better to seek column by column

DECLARE @TBL_NAME VARCHAR(100) = 'foo'
DECLARE @SEARCH_VALUE VARCHAR(100) = 'bar'
DECLARE @QUERY VARCHAR(MAX) = ''
SELECT
    @QUERY += '
    IF EXISTS (
        SELECT TOP 1 1 FROM ' + @tbl_name + '
        WHERE ' + NAME + ' = ''' + @SEARCH_VALUE + '''
        )
            PRINT ''The value exists in the column ' + name + '''
'
FROM SYS.COLUMNS
WHERE
    TYPE_NAME(SYSTEM_TYPE_ID) IN ('CHAR', 'VARCHAR', 'CHAR', 'VARCHAR')
    AND OBJECT_ID = OBJECT_ID(@TBL_NAME)
--PRINT @QUERY
EXEC(@QUERY)

Answer 2

I've worked with BornToCode's answer and this script generates the queries to find a value in all columns of type varchar for any view (can be table) of the database:

DECLARE @id INT
declare @name nvarchar(30)
DECLARE @getid CURSOR
declare @value nvarchar(30)
set @value = 'x'

SET @getid = CURSOR FOR
SELECT object_id,name
FROM   sys.views

OPEN @getid
FETCH NEXT
FROM @getid INTO @id, @name
WHILE @@FETCH_STATUS = 0
BEGIN

---------

SELECT  'SELECT * from ' + @name + ' where ''' + @value + ''' in (' + 
    STUFF((
    SELECT ', ' + c.name 
    FROM sys.columns  c
    JOIN sys.types AS t ON c.user_type_id=t.user_type_id
    WHERE t.name = 'varchar'--  AND t.name != 'bit' AND t.name !='date' AND t.name !='datetime'
        AND object_id =(SELECT object_id FROM sys.views WHERE name=@name)
    FOR XML PATH('')),1,2,'')
    + ')' as 'query'

------
FETCH NEXT
    FROM @getid INTO @id, @name

END

CLOSE @getid
DEALLOCATE @getid

Answer 3

--Obtain object_id
SELECT object_id FROM sys.tables WHERE name = <your_table>


--look for desired value in specified columns using below syntax
SELECT * FROM <your_table> WHERE <VALUE_YOU_SEARCH_FOR> in
(SELECT name FROM sys.tables WHERE object_id = <your_table_object_id>
and name like '<if_you_have_multiple_columns_with_same_name_pattern>')

Answer 4

I took the idea from ubaid ashraf's answer, but made it actually work. Just change MyTableName here:

SELECT STUFF((
    SELECT ', ' + c.name 
    FROM sys.columns  c
    JOIN sys.types AS t ON c.user_type_id=t.user_type_id
    WHERE t.name != 'int'  AND t.name != 'bit' AND t.name !='date' AND t.name !='datetime'
        AND object_id =(SELECT object_id FROM sys.tables WHERE name='MyTableName')
    FOR XML PATH('')),1,2,'')

You could tweak it to your needs and add or remove conditions from the where column (the 't.name != 'int' AND t.name != 'bit' etc. part), e.g. add 't.name != 'uniqueidentifier'' to avoid getting Conversion failed when converting the varchar value 'myvalue' to data type int type of errors..

Then copy paste the result into this query (otherwise it didn't work):

SELECT * from MyTableName where 'foo' in (COPY PASTE PREVIOUS QUERY RESULT INTO HERE)

Answer 5

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%'

Answer 6

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

Answer 7

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

Answer 8

You can use in:

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