Need Sql Query Help

Mohit Kala

8y846

I Need Sql Query For Search Keyword From all columns multiple tables

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nikunj Satasiya8y ago
Accepted answer
You Should Try this Query
SELECT id, 'messages' as 'table' FROM #Tbl_Msg
WHERE content LIKE '%keyword%'
OR title LIKE '%keyword%'
UNION
SELECT id, 'topics' as 'table' FROM #Tbl_topics
WHERE content LIKE '%keyword%'
OR title LIKE '%keyword%'
UNION
SELECT id, 'comments' as 'table' FROM #Tbl_comments
WHERE content LIKE '%keyword%'
OR title LIKE '%keyword%'
Pinakee Das8y ago
You can try the following query.
SET NOCOUNT ON;
DECLARE @searchstring NVARCHAR(255);
SET @searchstring = '%%';
--Replace the  with the search keyword
DECLARE @sql NVARCHAR(max);
SELECT @sql = STUFF((
SELECT ' UNION ALL SELECT ''' + TABLE_NAME + ''' AS [table], ''' + COLUMN_NAME + ''' AS [column], ' + COLUMN_NAME + ' AS [value]' +
' FROM ' + TABLE_SCHEMA + '.' + TABLE_NAME +
' WHERE ' + COLUMN_NAME + ' LIKE ''' + @searchstring + ''''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE in ('nvarchar', 'varchar')
FOR XML PATH('')
) ,1, 11, '');
EXECUTE sp_executesql @sql;
Mohit Kala8y ago
but i want to search in the table without using column name
for example
i am searching keyword mvc then display all the keyword with in my table
Nikunj Satasiya8y ago
As you can see in given Example there is LIKE Operator are used to search specified pattern withn in column.

Actually, The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards used in conjunction with the LIKE operator:

% - The percent sign represents zero, one, or multiple characters
_ - The underscore represents a single character

If you using MS Access database they there is need to be use a question mark (?) instead of the underscore (_).

Now Talking about Union Oprator

So, Generally Union operator is used to combine the result-set of two or more SELECT statements.

There are several conditions for use of Union in Sql Server which you need to follow as like

Each SELECT statement within UNION must have the same number of columns, The columns must also have similar data types and The columns in each SELECT statement must also be in the same order and etc.

Generally by default the UNION operator will selects only distinct values If you want duplicate records then you need to use UNION ALL

For brief example you should go with google search.

I hope this explanation will helpful to you.
Amit Kumar8y ago
Hi,
Sir plase explain brifely.