How to get a list of StopWords used in my FullText Catalog?

Question

Is there a way to get the StopWord list that my SQL Server 2008 FullText Catalog is using? And use it, in my C# codebehind?

I want to use it in a ASP.NET page that I use to search terms and highlight them.

The search page and the highlight are already working fine, but I want to improve the highlight. I don't want to highlight a word that is on my StopWord list.

Answer 1

In sql server management studio if you ask the properties from the fulltext index you can see which stoplist it uses. See here.

You can then use the system views sys.fulltext_stoplists and sys.fulltext_stopwords to get the list of stopwords.

Answer 2

It appears that the active stoplist in use has been removed from the GUI in later versions of SQL - so the correct answer from Sem is now outdated. Nowhere in the SQL Server Management Studio can I find which stoplist is in use for a particular Full Text Catalog.

After quite a bit of digging, the following query will easily provide which stoplist is used for each fulltext catalog:

select so.name as tableName, sfc.name as fullTextCatalogName, sfi.is_enabled, sfi.stoplist_id, sfs.name as stoplistName
from sys.fulltext_indexes as sfi
         left join sys.objects as so on so.object_id = sfi.object_id
         left join sys.fulltext_catalogs as sfc on sfc.fulltext_catalog_id = sfi.fulltext_catalog_id
         left join sys.fulltext_stoplists as sfs on sfi.stoplist_id = sfs.stoplist_id

So if stoplist_id is 0 - this indicates that this catalog is using the "default" system stoplist. If stoplist_id is NULL, this indicates no stoplist is in use (i.e. ALTER FULLTEXT INDEX ON {{TABLENAME}} SET STOPLIST = OFF).

And as indicated in another answer - if you want to additionally list WHAT stopwords are in the default system stoplist for a given language (assuming English here), you can:

SELECT * FROM sys.fulltext_system_stopwords WHERE language_id=1033

... see list with ID's of user defined stoplists:

SELECT * from sys.fulltext_stoplists

... and if you want to see what stopwords are in a user defined stoplist:

SELECT * from sys.fulltext_stopwords where language_id = 1033 and stoplist_id = {{a_valid_stoplist_id}}

I hope this helps, as I had to fix some of this on my application - and started really scratching my head trying to find where the active stoplist for an index was located - as I was used to just right click -> properties as worked in older versions of MSSQL...

Answer 3

SELECT * FROM sys.fulltext_stopwords |
SELECT * FROM sys.fulltext_system_stopwords

you can filter which stoplist you return by including the language code in a where clause

e.g. SELECT * FROM sys.fulltext_system_stopwords WHERE language_id=1033

(id 1033 corresponds to syslanguages 'English')

Alternatively, these can be found under the 'Full-Text Stoplists' category within the 'Storage' group against a standard SQL database