how to search from all field in sql

Question

For example my table is here>>

CREATE TABLE tProject(  
name nvarchar(1000),
Goal ntext, 
Activities ntext,
Result ntext,
MonName nvarchar(1000),
MonGoal ntext,
MonActivities ntext,
MonResult ntext,
TotalFund nvarchar(100))

how to search from all ntext and nvarchar fields in onetime.

help me please

Answer 1

If you mean creating a select command against the table matching several columns.

select * from tProject where name like '%text%' or Goal like '%text%' or...;

Answer 2

You really have names that are 1000 characters long? Are the MonName, MonGoal, etc. columns really for Monday Name, Monday Goal? If so, they should be moved into another table and linked to this one. TotalFund sounds more like a numeric column than an nvarchar. Depending on the answers to these questions, you may want to start with redesigning the database after learning some basic DB design practices before it's too late.

Anyway, I think this is what you're looking for:

SELECT
    name,
    Goal 
    Activities,
    Result,
    MonName,
    MonGoal,
    MonActivities,
    MonResult,
    TotalFund
FROM
    tProject
WHERE
    name LIKE '%' + @search_string + '%' OR
    Goal LIKE '%' + @search_string + '%' OR
    Activities LIKE '%' + @search_string + '%' OR
    Result LIKE '%' + @search_string + '%' OR
    MonName LIKE '%' + @search_string + '%' OR
    MonGoal LIKE '%' + @search_string + '%' OR
    MonActivities LIKE '%' + @search_string + '%' OR
    MonResult LIKE '%' + @search_string + '%' OR
    TotalFun LIKE '%' + @search_string + '%'

You can also look into full text string searching, although I don't know if that will make it any simpler.