Yesterday I covered the basics around StopWords/StopLists in SQL Server 2008. Today we’ll look at some advanced queries you can use to get more information back about FullText Indexes, StopWords, and StopLists.
The first query simply returns a list of all of the catalogs in the system.
— Get current list of full text catalogs
select [name] as CatalogName
, path
, is_default
from sys.fulltext_catalogs
order by [name];
The next query returns a list of all the StopLists.
— Get the list of StopLists
select stoplist_id
, name
from sys.fulltext_stoplists;
This query returns a list of StopWords in the database. Note the linking to get the associated StopList name and language.
— Get list of StopWords
select sl.name as StopListName
, sw.stopword as StopWord
, lg.alias as LanguageAlias
, lg.name as LanguageName
, lg.lcid as LanguageLCID
from sys.fulltext_stopwords sw
join sys.fulltext_stoplists sl
on sl.stoplist_id = sw.stoplist_id
join master.sys.syslanguages lg
on lg.lcid = sw.language_id;
This next query gets a list of all of the stopwords that ship with SQL Server 2008. This is a nice improvement, you can not do this in SQL Server 2005.
— Get a list of the System provided stopwords
select ssw.stopword
, slg.name
from sys.fulltext_system_stopwords ssw
join sys.fulltext_languages slg
on slg.lcid = ssw.language_id;
My next query returns a list of all the Full Text Indexes in the database.
— List full text indexes
select c.name as CatalogName
, t.name as TableName
, idx.name as UniqueIndexName
, case i.is_enabled
when 1 then ‘Enabled’
else ‘Not Enabled’
end as IsEnabled
, i.change_tracking_state_desc
, sl.name as StopListName
from sys.fulltext_indexes i
join sys.fulltext_catalogs c
on i.fulltext_catalog_id = c.fulltext_catalog_id
join sys.tables t
on i.object_id = t.object_id
join sys.indexes idx
on i.unique_index_id = idx.index_id
and i.object_id = idx.object_id
left join sys.fulltext_stoplists sl
on sl.stoplist_id = i.stoplist_id
This query returns a list of all the document types SQL Server 2008 understands when they are placed in a varbinary(max) field.
— List all of the document types SQL Server 2008 will understand in varbinary(max) field
select document_type
, path
, [version]
, manufacturer
from sys.fulltext_document_types;
If your full text performance begins to suffer over time, you might want to check and see how many fragments exist. If you have multiple closed fragments, you should consider doing a REORGANIZE on the index (using alter fulltext index). This query will tell you how many fragments exist for your full text index.
— See how many fragments exist for each full text index.
— If multiple closed fragments exist for a table do a REORGANIZE to help performance
select t.name as TableName
, f.data_size
, f.row_count
, case f.status
when 0 then ‘Newly created and not yet used’
when 1 then ‘Being used for insert’
when 4 then ‘Closed ready for query’
when 6 then ‘Being used for merge inpurt and ready for query’
when 8 then ‘Marked for deletion. Will not be used for query and merge source’
else ‘Unknown status code’
end
from sys.fulltext_index_fragments f
join sys.tables t on f.table_id = t.object_id;
There you go, a handful of powerful queries to help you query and maintain the state of your full text indexes.
Arcane Code 
This is somthing I have been looking for a long time. Thanks!!!
How can one get which column(s) a full-text index is defined on? There is the unique_index_id field of the sys.fulltext_indexes catalog view, but this points to the unique index (eg. PK) used for defining the full-text index. There is no column in that catalog view pointing to the actual column in the table on which the ft index is defined.