How can I search all columns in a table?

Question

How can I search all columns of a table in SQL Server?

Evvy

where column1 = 'val' or column2 = 'val' or column etc etc If its just an adhoc search while you look into some problem, you could perhaps dump the table to csv and search that manually – Bedizen

please provide more information or examples of what you have tried already – Xenophanes

What is the type of the columns you want to search? Text? Int? Something else? Do you want to search all columns even if they have different types? – Afeard

There isn't any easier ways to do it? – Evvy

Mark Byers:Yes There Is nvarchar and int in my Table – Evvy

Is this a one-time or seldom-used query? You can use DMVs to generate SQL to search all string columns in a table or an entire database (or even all databases on a connection). – Koala

Answer 1

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....

Jonell

There isn't any easier ways to do it? – Evvy

Sometimes you actually have to do a bit of work and specify a complicated query, instead of trying to find shortcuts. This is one of them. – Jonell

Just Think If There is a lot columns in the table.Then It might be a lot of buggy Stored Procudure – Evvy

"Just Think If There is a lot columns in the table.Then It might be a lot of buggy Stored Procedure" So, the database is a mess? For a really big query, I'd copy the column headers to excel and edit the syntax in bulk, paste into notepad and remove unnecessary tabs etc, then paste into SQL query builder. Fast, but does not fix the real problem of messy DB. – Patina

@MarcB if i ihave 50 or 60 columns in MYSQL TABLE then how can i implement efficient searching. How can i check any keyword that the user enters is matches with any of the column value.i have using your second method but it takes more time to run. How can implement efficient way of searching ..?Can you please help me ? – Accessory

@MarcB I have 274 columns in my table. By your logic, I shouldn't even query anything, I should just "do the work" and look for matches manually across my 1.3 million rows. – Ensor

Answer 2

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

Princedom

you saved my time ty man ... this is the best soln Many thanks – Otocyst

Some edits to make it work – Aaberg

Answer 3

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

Neuroblast

Good answer Dylan, and if we remove the % signs, it will search for exactly the 'foo' bit. – Patina

Answer 4

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

Sooty

Answer 5

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.

Forthcoming

This is a really simple solution, which makes me like it all the more. To address your drawback you could make a materialized view with the column. You would also want to generalize the query with WHERE LIKE 'key word%' or LIKE '%key word' or LIKE '%key word%' – Continuant

Answer 6

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.)

Substantialize

Answer 7

I took @tca1130 code to improve the final resultset. I've added a sample query for each match:

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

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

SELECT   @SQL = 
 -- Column number of matches
 @SQL + 'SELECT CONVERT(VARCHAR(MAX),COUNT(*)) + '' matches in column ''+'''
 + C.name + '''+'' on table '' + ''' + SC.name + '.' + T.name + 
 ''' [Matches for '''+@SearchString+''': in values] '
-- Column query
 + ', ''SELECT ''+''' + C.name + '''+'' FROM '' + ''' + SC.name + '.[' + T.name + 
 '] WHERE  ''+'''  + C.name + '''+''     LIKE  ''''%'+@SearchString+'%'''''' AS  [Query]' 
 + '  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) 

Feeble