SELECT ...
FROM yourtable
WHERE 'val' IN (field1, field2, field3, field4, ...)

if you're looking for exact full-field matches. If you're looking for substring matches, you'll have to go about it the long way:

WHERE field1 LIKE '%val%' or field2 LIKE '%val%' etc....

You can even do it this way.. Dynamically creating the query..

CREATE PROC SearchSpecificTable
(
@SearchStr nvarchar(100)
)
AS
BEGIN

    SET NOCOUNT ON;
    
    DECLARE @columnName NVARCHAR(100), @SearchStr2 nvarchar(128)
    DECLARE @tableName NVARCHAR(100) = 'TARGET_TABLE'
    DECLARE @sql NVARCHAR(MAX) = 'SELECT * FROM ' + @tableName +' WHERE '
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
    
    DECLARE columns CURSOR FOR
    SELECT sys.columns.name FROM sys.tables
    INNER JOIN sys.columns ON sys.columns.object_id = sys.tables.object_id
    WHERE sys.tables.name = @tableName
    
    OPEN columns
        FETCH NEXT FROM columns
        INTO @columnName
        
        WHILE @@FETCH_STATUS = 0
        
        BEGIN
        
            SET @sql = @sql + @columnName + ' LIKE ' + @SearchStr2 + ' OR '
            FETCH NEXT FROM columns
            INTO @columnName    
        
        END
    
    CLOSE columns;    
    DEALLOCATE columns;
    
    SET @sql = LEFT(RTRIM(@sql), LEN(@sql) - 2) -- remove last OR
    
    EXEC(@sql)
END

You can of course change DECLARE @tableName NVARCHAR(100) = 'TARGET_TABLE' into parameter of the stored procedure.

You invoke it with: EXEC SearchSpecificTable @SearchStr = 'needle_to_find';

I don't believe there is any shortcut for this, you have to specify out the list of columns you wish to search. I would argue that if you find yourself trying to do this alot, you probably could improve upon the DB design.

SELECT * 
FROM MyTable 
WHERE Col1 LIKE '%foo%' OR 
      Col2 LIKE '%foo%' OR  
      Col3 LIKE '%foo%' OR 
      Col4 LIKE '%foo%' OR 
      Col5 LIKE '%foo%' OR 
      Col6 LIKE '%foo%'

I can't take credit, but I found this and it works perfectly. You can add additional data types to the WHERE clause to include other types, as well as adding a schema name to the JOIN (commented below) to limit your search, if needed.

DECLARE @SQL VARCHAR(MAX) 
DECLARE @SearchString VARCHAR(100) 
SET @SQL='' 

-- ------------------------------------------ 
-- Enter the string to be searched for here : 
SET @SearchString='' 
-- ------------------------------------------ 

SELECT   @SQL = @SQL + 'SELECT CONVERT(VARCHAR(MAX),COUNT(*)) + '' matches in column ''+'''
     + C.name + '''+'' on table '' + ''' + SC.name + '.' + T.name + 
     ''' [Matches for '''+@SearchString+''':] FROM ' + 
     QUOTENAME(SC.name) + '.' + QUOTENAME(T.name) + ' WHERE ' + QUOTENAME(C.name) + 
     ' LIKE ''%' + @SearchString + 
     '%'' HAVING COUNT(*)>0 UNION ALL ' +CHAR(13) + CHAR(10) 

FROM     sys.columns C 
JOIN     sys.tables T ON C.object_id=T.object_id 
JOIN     sys.schemas SC ON SC.schema_id=T.schema_id -- AND SC.name = ''
JOIN     sys.types ST ON C.user_type_id=ST.user_type_id 
JOIN     sys.types SYST ON ST.system_type_id=SYST.user_type_id
    AND ST.system_type_id=SYST.system_type_id 

WHERE    SYST.name IN ('varchar','nvarchar','text','ntext','char','nchar') 

ORDER BY T.name, C.name 

-- Strip off the last UNION ALL 

IF LEN(@SQL)>12 
  SELECT @SQL=LEFT(@SQL,LEN(@SQL)- 12) 

EXEC(@SQL) 
--PRINT @SQL

Reference: https://www.sqlmatters.com/Articles/Searching%20all%20columns%20in%20all%20tables%20in%20a%20database.aspx

I did this before in a project. The best way to search all columns in SQL would be to create a new column in the table and paste each columns text/data into it like so:

table:
|---------------------------------------------------|
| column1 | column2 | search_column |
|---------------------------------------------------|
| fooxxxxx | barxxxx | fooxxxxx barxxxx |
----------------------------------------------------|

SELECT search_column FROM table1 LIKE %key word%

It works and gets the job done without writing lots of SQL code. It is also much faster to run this type of query. The only drawback is that when the table is updated and created the search_column has to be constructed.

I found a fast way after reading the answer of Jose Hernandez Naranjo using a subquery. This has the benefit that we do not have to add an extra column to the database.

Let's suppose we have a table person with the columns PersonID, Firstname, Name, Cellphone, Email and Place, which should all be search and The full name including the Place should be outputet. The code would than look the following way:

SELECT Firstname, Name, Place 
FROM person NATURAL JOIN 
  (SELECT PersonID, CONCAT(Firstname, ' ', Name, ' ', Cellphone, ' ',
   Email, ' ', Place) search_string FROM person) as all_in_one
WHERE search_string LIKE '%Hans%' AND search_string LIKE '%Muster%'

If you are using the SQL from another language like PHP, you can add the part behind the WHERE using a loop. Here an example from PHP:

$q = $_GET['q'];
$words = explode(" ", $q);
foreach($words as $word){
  if(is_null($search_query)){
     $search_query = "search_string LIKE '%$word%'";
  }else{
     $search_query .= " AND search_string LIKE '%$word%'";
  }     
}

(Attention: This example does not do any check of the GET Variable which could be potentially used for SQL injection.)