SQL Full-Text – Advanced queries


Full-text

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

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

To see current database existing Stoplists, type:

SELECT sfts.stoplist_id
,sfts.name
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
,sftl.name
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
,sftdt.path
,sftdt.version
,sftdt.manufacturer 
FROM sys.fulltext_document_types sftdt

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

SELECT st.name AS [Table Name]
,sftif.data_size
, 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'
END
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
http://arcanecode.com/2008/06/03/advanced-queries-for-using-sql-server-2008-full-text-search-stopwords-stoplists/
https://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/
http://hspinfo.wordpress.com/2011/07/27/microsoft-sql-server-2008-fulltext-and-stoplist-association-manage-multiple-stoplists-fulltext-index/

Tested With
SQL Server 2008 R2
SQL Server 2012

Advertisements
Categories: SQL Full-Text | Leave a comment

Post navigation

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: