columns in full text query

Hi
Is there a system query that tells me which columns are a fulltext index for
a particular catalog?
Cheers
James
you could try sp_help_fulltext_columns in a full text enabled database which
will tell you all the tables and the columns in these tables which are being
full text indexed.
Or you could use sp_help_fulltext_tables_cursor and pass it the catalog name
and then iterate the results set as illustrated below. In the below example
the catalog name is test.
USE pubs
GO
DECLARE @.mycursor CURSOR
EXEC sp_help_fulltext_tables_cursor @.mycursor OUTPUT, 'test'
FETCH NEXT FROM @.mycursor
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
FETCH NEXT FROM @.mycursor
END
CLOSE @.mycursor
DEALLOCATE @.mycursor
GO
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:%23WqhRpGsEHA.3712@.TK2MSFTNGP15.phx.gbl...
> Hi
> Is there a system query that tells me which columns are a fulltext index
for
> a particular catalog?
> Cheers
> James
>

columns in full text query

Hi
Is there a system query that tells me which columns are a fulltext index for
a particular catalog?
Cheers
JamesFrom the BOL:
sp_help_fulltext_columns
Returns the columns designated for full-text indexing.
Rick Sawtell
MCT, MCSD, MCDBA
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:uZCdpjGsEHA.1272@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a system query that tells me which columns are a fulltext index
for
> a particular catalog?
> Cheers
> James
>

columns in full text query

Hi
Is there a system query that tells me which columns are a fulltext index for
a particular catalog?
Cheers
JamesFrom the BOL:
sp_help_fulltext_columns
Returns the columns designated for full-text indexing.
Rick Sawtell
MCT, MCSD, MCDBA
"James Brett" <james.brett@.unified.co.uk> wrote in message
news:uZCdpjGsEHA.1272@.TK2MSFTNGP09.phx.gbl...
> Hi
> Is there a system query that tells me which columns are a fulltext index
for
> a particular catalog?
> Cheers
> James
>

Column Names of Fulltext search result

I was trying to do full text search. I have no problem to do the search as
following:
select MemberID, Surname, Firstname from members where FREETEXT(*, N'moore')
It returned all rows for any column containing values that match the
meaning, but not the exact wording, of the text 'moore'.
My problem is that my client want to know the name(s) of the column(s) which
the keyword 'moore' was found. How can I get the required info?
And I also tried FREETEXTTABLE, which only returns a relevance ranking value
(RANK) and full-text key (KEY) for each row. Same things using CONTAINS or
CONTAINSTABLE
Thanks in advance.
To get an exact search, ie moore, but not moores wrap your freetext search
in double quotes or use contains, ie
select MemberID, Surname, Firstname from members where FREETEXT(*,
N'"moore"')
You can't easily get the column where the hit occurs. You would have to do
something like this
create table members(MemberID int identity not null, surname varchar(20),
firstname varchar(20), constraint memberspk primary key (memberid))
GO
insert into members(surname, firstname) values('moore','moore')
insert into members(surname, firstname) values('moore','dave')
insert into members(surname, firstname) values('dave','moore')
insert into members(surname, firstname) values('dave','dave')
GO
sp_fulltext_database 'enable'
GO
create fulltext index on members(surname, firstname) key index memberspk
GO
select *, case
when charindex('moore',surname)>0 and charindex('moore',firstname)=0 then
'surname'
when charindex('moore',surname)=0 and charindex('moore',firstname)>0 then
'Firstname'
when charindex('moore',surname)>0 and charindex('moore',firstname)>0 then
'both'
else 'not sure'
end From members where contains(*,'moore')
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"X. Zhang" <XZhang@.discussions.microsoft.com> wrote in message
news:5C88126C-FC9F-4281-BAA7-7FC3AAE2E0BD@.microsoft.com...
>I was trying to do full text search. I have no problem to do the search as
> following:
> select MemberID, Surname, Firstname from members where FREETEXT(*,
> N'moore')
> It returned all rows for any column containing values that match the
> meaning, but not the exact wording, of the text 'moore'.
> My problem is that my client want to know the name(s) of the column(s)
> which
> the keyword 'moore' was found. How can I get the required info?
> And I also tried FREETEXTTABLE, which only returns a relevance ranking
> value
> (RANK) and full-text key (KEY) for each row. Same things using CONTAINS or
> CONTAINSTABLE
> Thanks in advance.
|||Hilary,
Thank you for your reply. I thought about this way too, but I have problem
with it. I had more than 50 columns to be searched, which means I have to
'when' more than 50 times, actually lots more than 50 times if I need the
column combinations, such as your 'both' case.
I was wondering if there is a straight forward way to do so. If no, I guess
I have to do some programming...
Thanks,
"Hilary Cotter" wrote:

> To get an exact search, ie moore, but not moores wrap your freetext search
> in double quotes or use contains, ie
> select MemberID, Surname, Firstname from members where FREETEXT(*,
> N'"moore"')
> You can't easily get the column where the hit occurs. You would have to do
> something like this
> create table members(MemberID int identity not null, surname varchar(20),
> firstname varchar(20), constraint memberspk primary key (memberid))
> GO
> insert into members(surname, firstname) values('moore','moore')
> insert into members(surname, firstname) values('moore','dave')
> insert into members(surname, firstname) values('dave','moore')
> insert into members(surname, firstname) values('dave','dave')
> GO
> sp_fulltext_database 'enable'
> GO
> create fulltext index on members(surname, firstname) key index memberspk
> GO
> select *, case
> when charindex('moore',surname)>0 and charindex('moore',firstname)=0 then
> 'surname'
> when charindex('moore',surname)=0 and charindex('moore',firstname)>0 then
> 'Firstname'
> when charindex('moore',surname)>0 and charindex('moore',firstname)>0 then
> 'both'
> else 'not sure'
> end From members where contains(*,'moore')
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "X. Zhang" <XZhang@.discussions.microsoft.com> wrote in message
> news:5C88126C-FC9F-4281-BAA7-7FC3AAE2E0BD@.microsoft.com...
>
>
|||You could also limit your FT query to a certain column.
For example
select MemberID, Surname, Firstname from members where
contains(surname,'moore')
and do the same for every fulltext indexed column in your table and then
combine the results.
Thanks
"X. Zhang" wrote:
[vbcol=seagreen]
> Hilary,
> Thank you for your reply. I thought about this way too, but I have problem
> with it. I had more than 50 columns to be searched, which means I have to
> 'when' more than 50 times, actually lots more than 50 times if I need the
> column combinations, such as your 'both' case.
> I was wondering if there is a straight forward way to do so. If no, I guess
> I have to do some programming...
> Thanks,
> "Hilary Cotter" wrote: