Search a keyword and retrieving column where it was found?

I have some tables, Ex.: Car(id, NameCar) and Person(id, PersonName, Address, etc)

I need to search for a keyword and say in which开发者_StackOverflow社区 columns I found it.

Example: Search for Civic, return Person with id 10 have Civic at Car Name, and at Address Name.

How can I do this with good performance?

make a view that contains all the relevant columns. then query that view.

it would help to show specific data values and expected results.

alternately you cuold use a structure with UNIONs and indicate which union the row comes from.. similar to this:

select name, 1 from person where name like '%civic%'
union
select street,2 from address where street like '%civic%'

..

etc

I'm not sure how the performance rates with other options, but when we needed that type of feature we created a full-text catalog. You can find this feature in SSMS under [DatabaseName] - Storage - Full Text Catalogs. Define which fields you want to index and then let it build. Then you can query against the index.

Here's a good article to supplement my terrible description: http://www.developer.com/db/article.php/3446891/Understanding-SQL-Server-Full-Text-Indexing.htm

I used Full Text Index for better performance:

SELECT
    A.UserId,
    CASE WHEN CONTAINS(C.nm_colaborador, @palavraChave) THEN CAST(1 as bit) ELSE CAST(0 as bit) END As Nome,
    CASE WHEN CONTAINS(C.dc_cidades, @palavraChave) THEN CAST(1 as bit) ELSE CAST(0 as bit) END As Cidade
FROM
    Curriculo A
    INNER JOIN CONTAINSTABLE(Curriculo_Busca, *, @palavraChave) B ON A.UserId = B.[KEY]
    INNER JOIN Curriculo_Busca C ON B.[KEY] = C.UserId