Fulltext search with MS SQL

[pub00515]

Hello,

I am trying to implement a fulltext search on a MS SQL database. While I can query each table independently for a specific word, I didn't find a method to query the entire collection of tables for my search key. Is there a way to do this (using the fulltext catalog for example)?

Thanks!

Mark

 

[mrdenny]

You would need to use CONTAINSTABLE and INNER JOIN the CONTAINSTABLE statements together.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[pub00515]

Hi Denny,

thanks for your answer! I'm pretty new to this so I don't really see the solution yet.

My problem is that I don't know the names of my tables (nor the ID's) and I don't know how many tables there are (could be 2, could be 100). I just want to perform a query where I can find out in which tables I can find the word "test" for example. At this point I don't care in which column the data is or how many columns are indexed. Is this possible?

Thanks again,

Mark

 

[victorashi]

you should try doing all dinamicly. it`s hard , but when you do that, many of your problems will be solved.

 

[mrdenny]

Using dynamic SQL can be very complex, and it requires opening up the security on the objects quite a bit.

If you work in a shop where the security is locked down dynamic SQL may not be an option.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[DotNetBlocks]

Hello, what is the diffrence between a CONTAINSTABLE and CONTAINS? Just curious, I am new to using fulltext catalogs.

Babloome

 

[mrdenny]

With CONTAINS you have to tell SQL which column to look at. CONTAINS table looks at all the columns in the table.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mrdenny]

With CONTAINS you have to tell SQL which column to look at. CONTAINSTABLE looks at all the columns in the table.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[DotNetBlocks]

So which is faster?

contains(*,'"vb" and ".net"')

containsTable('"vb" and ".net"')

If it matters i am using sql 2005

Thanks for all your help.

Babloome

 

[mrdenny]

They should be the same as they are doing the same thing. Check the execution plan to be sure.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com