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

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.

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...

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