FullTest search in tSQL

fts-t.sql

-- https://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/
IF OBJECT_ID (N'ProductDocs', N'U') IS NOT NULL  
DROP TABLE ProductDocs  
GO
CREATE TABLE ProductDocs (  
  DocID INT NOT NULL IDENTITY,  
  DocTitle NVARCHAR(50) NOT NULL,  
  DocFilename NVARCHAR(400) NOT NULL,  
  FileExtension NVARCHAR(8) NOT NULL,  
  DocSummary NVARCHAR(MAX) NULL,  
  DocContent VARBINARY(MAX) NULL,  
  CONSTRAINT [PK_ProductDocs_DocID] PRIMARY KEY CLUSTERED (DocID ASC)  
)  
GO
--INSERT INTO ProductDocs  
--(DocTitle, DocFilename, FileExtension, DocSummary, DocContent)  
--SELECT Title, FileName, FileExtension, DocumentSummary, Document  
--FROM Production.Document  
--GO

CREATE FULLTEXT CATALOG ProductFTS 
WITH ACCENT_SENSITIVITY = OFF

CREATE FULLTEXT INDEX ON ProductDocs  
(DocSummary, DocContent TYPE COLUMN FileExtension LANGUAGE 1033)  
KEY INDEX PK_ProductDocs_DocID  
ON ProductFTS  
WITH STOPLIST = SYSTEM


CREATE FULLTEXT STOPLIST ProductSL 
FROM SYSTEM STOPLIST;

SELECT stoplist_id, name FROM sys.fulltext_stoplists

SELECT stopword FROM sys.fulltext_stopwords  
WHERE stoplist_id = 5 AND language_id = 1033

ALTER FULLTEXT STOPLIST ProductSL  
ADD 'nuts' LANGUAGE 1033; 

SELECT special_term, display_term  
FROM sys.dm_fts_parser  
  (' "testing for fruit and nuts, any type of nut" ', 1033, 5, 0)

ALTER FULLTEXT INDEX ON ProductDocs  
SET STOPLIST ProductSL

SELECT * FROM sys.fulltext_languages ORDER BY lcid