Need Sql Query Help

Question

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

Nikunj Satasiya · 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 Das · Answer

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 Kala · Answer

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 Satasiya · Answer

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 Kumar · Answer

Hi, Sir plase explain brifely.