x
login about faq Site discussion (meta-askssc)

Can I full-text search a temporary table?

If I populate a temporary table or a table variable, is there a way to search it with CONTAINS?

more ▼

asked Nov 30 '09 at 04:37 PM in Default

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 74 78 82

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You can search a table using CONTAINS, if the table has a full-text index. Whether you can create a full-text index on a temporary table or table variable is another question.

I tried this -->

CREATE TABLE #sp_who3 
( 
    SPID INT NOT NULL, 
    Status VARCHAR(32) NULL, 
    Login SYSNAME NULL, 
    HostName SYSNAME NULL, 
    BlkBy SYSNAME NULL, 
    DBName SYSNAME NULL, 
    Command VARCHAR(32) NULL, 
    CPUTime INT NULL, 
    DiskIO INT NULL, 
    LastBatch VARCHAR(14) NULL, 
    ProgramName VARCHAR(32) NULL, 
    SPID2 INT 
) 

CREATE UNIQUE INDEX ui_SPID ON #sp_who3(SPID);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON #sp_who3(Command) 
   KEY INDEX ui_SPID 
   WITH STOPLIST = SYSTEM;

The CREATE FULLTEXT INDEX failed with: Invalid object name '#sp_who3'

more ▼

answered Nov 30 '09 at 07:49 PM

Jim Orten gravatar image

Jim Orten
646 6 8 10

The code above works if it's not a temp table. I'm betting if you can't do it with a temp table, you can't do it with a table variable, but I haven't tested that.

Nov 30 '09 at 07:59 PM Jim Orten
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x912
x43

asked: Nov 30 '09 at 04:37 PM

Seen: 2072 times

Last Updated: Nov 30 '09 at 04:37 PM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.