Geek Answers Handbook

How can I search all columns in a table?

How can I search all table columns in SQL Server?

+10
source share
6 answers
SELECT ...
FROM yourtable
WHERE 'val' IN (field1, field2, field3, field4, ...)

if you are looking for exact full field matches. If you are looking for subscript matches, you have a long way to go:

WHERE field1 LIKE '%val%' or field2 LIKE '%val%' etc....
+19
source

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

SET NOCOUNT ON;

DECLARE @searchText NVARCHAR(100) = 'Test'
DECLARE @columnName NVARCHAR(100)
DECLARE @tableName NVARCHAR(100) = 'Accounts'
DECLARE @sql NVARCHAR(1000) = 'SELECT * FROM ' + @tableName +' WHERE '

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 ''%' + @searchText + '%'' OR '
    FETCH NEXT FROM columns
    INTO @columnName    

END

CLOSE columns;    
DEALLOCATE columns;

SET @sql = LEFT(RTRIM(@sql), LEN(@sql) - 2)

EXEC(@sql)
+3
source

, , , . , , , , .

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

. SQL - / :

:
| ------------------------------------------------- - |
| column1 | 2 | search_column |
| ------------------------------------------------- - |
| fooxxxxx | barxxxx | fooxxxxx barxxxx |
-------------------------------------------------- - |

SELECT search_column FROM table1 LIKE% %

SQL. , . , search_column.

+1

, , . WHERE, , JOIN (. ), .

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

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

+1

, . , .

, person PersonID, , , , , , , , . :

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

SQL , PHP, WHERE, . 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%'";
  }     
}

(: GET, SQL-.)

+1

More articles:


All Articles