How to search a given text in a table in sql server

Question

I want to search a text in a table without knowing its attributes. Example : I have a table Customer,and i want to search a record which contains 'mohit' in any field without knowing its column name.

Answer 1

If you wanna make a full search on the table, you must surely be knowing the structure of the table. Considering the table has fields id, name, age, and address, then your SQL Query should be like:

SELECT * FROM `Customer`
    WHERE `id` LIKE '%mohit%'
    OR `name` LIKE '%mohit%'
    OR `age` LIKE '%mohit%'
    OR `address` LIKE '%mohit%';

Answer 2

You are looking for Full Text Indexing

Example using the Contains

select ColumnName from TableName 
Where Contains(Col1,'mohit') OR contains(col2,'mohit')

NOTE - You can convert the above Free text query into dynamic Query using the column names calculated from the sys.Columns Query

Also check below

FIX: Full-Text Search Queries with CONTAINS Clause Search Across Columns

Also you can check all Column Name From below query

Select Name From sys.Columns Where Object_Id = 
                   (Select Object_Id from sys.Tables Where Name = 'TableName')

Double-WildCard LIKE statements will not speed up the query.

Answer 3

Mohit, I'm glad you devised the solution by yourself.

Anyway, whenever you again face an unknown table or database, I think it will be very welcome the code snippet I just posted here.

Ah, one more thing: the answers given did not addressed your problem, did they?