How to search about a specific value in all columns in the table?

I have a table (students_all) that contains 4 columns as below :-

• student_name
• age
• student_id
• class

Now how can I search about a specific value such as 'left' in all columns in one query using SQL Server 2008.

I mean how can make something like Excel (Ctrl+F) to find any value in all columns.

8 Answers

There are many ways to check to find something like that in one SQL for a special table, So I suggest this way:

SELECT * FROM students_all WHERE student_name + age + student_id + class LIKE '%left%'; 

But for a dynamic way I use this:

DECLARE @TableName nvarchar(256) = 'Table_1'; DECLARE @Find nvarchar(50) = '1'; DECLARE @sql nvarchar(max) = ''; SELECT @sql = @sql + CASE WHEN @sql = '' THEN '' ELSE '+ ' END + 'CONVERT(nvarchar(max), ISNULL(' + cols.COLUMN_NAME + ', '''')) COLLATE DATABASE_DEFAULT ' FROM INFORMATION_SCHEMA.COLUMNS cols WHERE cols.TABLE_NAME = @TableName; SELECT @sql = 'SELECT * FROM ' + @TableName + ' WHERE ' + @sql + ' LIKE ''%' + @Find + '%'''; -- Or if you want to add other field types you can use this instead: --SELECT @sql = 'SELECT * FROM ' + @TableName + ' WHERE CAST(' + @sql + ' AS NVARCHAR(MAX)) LIKE ''%' + @Find + '%'''; EXEC(@sql); 

SELECT * FROM students_all as t WHERE t.student_name == 'left' OR t.age == 'left' OR t.student_id == 'left' OR t.class == 'left' 

There isn't a "fast" way to check every colums at one time, you need to check 1by1 every colum!

What i understand is that u want data which have value left in any of column of table

For that you can use

SELECT * FROM students_all WHERE student_name == 'left' OR age == 'left' OR student_id == 'left' OR class == 'left' 

This will check give you the rows which have at least one column of value left

But i you want to get data which contain the word left the you need to change all those for conditions in WHERE to COLULMN_NAME LIKE %left%

Hopefully this will help.

SELECT * FROM students_all WHERE 'left' in (student_name , age ,student_id , class); 

It will provide you the row where Left is present

I think this earlier post is what the OP's trying to refer to. It requires modification, however, to search only from 1 specific table.

SELECT * FROM students_all as std WHERE std.student_name = 'left' OR std.age = 'left' OR std.student_id = 'left' OR std.class = 'left' 

Modifying @shA.t's answer to allow for column names with spaces.

DECLARE @TableName nvarchar(256) = 'TableName'; DECLARE @Find nvarchar(50) = 'SearchText'; DECLARE @sql nvarchar(max) = ''; SELECT @sql = @sql + CASE WHEN @sql = '' THEN '' ELSE ' OR ' END + '[' + cols.COLUMN_NAME +']' + ' LIKE ''%' + @Find +'%''' FROM INFORMATION_SCHEMA.COLUMNS cols WHERE cols.TABLE_NAME = @TableName; select @sql = 'SELECT * FROM ' + @TableName + ' WHERE ' + @sql execute(@sql) 

select * from students_all where left ( student_name age student_id class 

if all the columns are string type