SQL Server SELECT where any column contains 'x'
You can use in
:
SELECT *
FROM testing
WHERE 'foo' in (col1, col2, col3, . . . );
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
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