question

Steve Jones - Editor avatar image
Steve Jones - Editor asked

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?

t-sqlfull-text
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Jim Orten avatar image
Jim Orten answered

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'

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jim Orten avatar image Jim Orten commented ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.