SQL Full-Text

SQL Full-Text – Advanced queries


Advanced queries
To see current database existing Full-Text catalogs, type:

SELECT sftc.name
FROM sys.fulltext_catalogs sftc
ORDER BY sftc.[name]

To see current database existing Stoplists, type:

SELECT sfts.stoplist_id
FROM sys.fulltext_stoplists sfts

To see current database Full-Text catalog in detail, type:

SELECT sftc.name AS [Catalog Name]
,st.name AS [Table Name]
,si.name AS [Unique Index Name]
,CASE sfti.is_enabled 
WHEN 1 THEN 'Enabled'
ELSE 'Disabled'
END AS [Is Enabled]
,sfti.change_tracking_state_desc AS [Change Tracking]
,sftsl.name AS [Stoplist Name]
FROM sys.fulltext_indexes sfti
INNER JOIN sys.fulltext_catalogs sftc
ON sfti.fulltext_catalog_id = sftc.fulltext_catalog_id
INNER JOIN sys.tables st 
ON st.object_id = sfti.object_id
INNER JOIN sys.indexes si 
ON sfti.unique_index_id = si.index_id
AND sfti.object_id = si.object_id
LEFT OUTER JOIN sys.fulltext_stoplists sftsl
ON sfti.stoplist_id = sftsl.stoplist_id

To see current database all Stopwords from all Stoplists, type:

SELECT sftsl.name AS [Stoplist Name]
,sftsw.stopword AS [Stopword]
,ssl.alias AS [Language Alias]
,ssl.name AS [Language Name]
,ssl.lcid AS [Language LCID]
FROM sys.fulltext_stopwords sftsw
INNER JOIN sys.fulltext_stoplists sftsl 
ON sftsw.stoplist_id = sftsl.stoplist_id
INNER JOIN master.sys.syslanguages ssl
ON sftsw.lcid = ssl.language_id

To see system default Stopwords, type:

SELECT sftstw.stopword
FROM sys.fulltext_system_stopwords sftssw
INNER JOIN sys.fulltext_languages sftl
ON sftssw.lcid = sftl.language_id

To see all ‘understandable’ document that can be used in varbinary datatype, type:

SELECT sftdt.document_type
FROM sys.fulltext_document_types sftdt

To see current database Full-Text index fragments, type:

SELECT st.name AS [Table Name]
, sftif.row_count
,CASE sftif.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'
FROM sys.fulltext_index_fragments sftif
INNER JOIN sys.tables st
ON sftif.table_id = st.object_id

Note: If multiple closed fragments exist for a table performance may be affected. Reorganize de Full-Text catalog.

In Depth

Tested With
SQL Server 2008 R2
SQL Server 2012

Categories: SQL Full-Text | Leave a comment

Blog at WordPress.com.