sys.dm_fts_index_keywords (Transact-SQL)

db_id('database_name')

A call to the DB_ID() function. This function accepts a database name and returns the database ID, which sys.dm_fts_index_keywords uses to find the specified database. If database_name is omitted, the current database ID is returned.

object_id('table_name')

A call to the OBJECT_ID() function. This function accepts a table name and returns the table ID of the table containing the full-text index to inspect.

The information returned by sys.dm_fts_index_keywords is useful for finding out the following, among other things:

• Whether a keyword is part of the full-text index.

• How many documents or rows contain a given keyword.

• The most common keyword in the full-text index:

  • document_count of each keyword_value compared to the total document_count, the document count of 0xFF.

  • Typically, common keywords are likely to be appropriate to declare as stopwords.

Note

The document_count returned by sys.dm_fts_index_keywords may be less accurate for a specific document than the count returned by sys.dm_fts_index_keywords_by_document or a CONTAINS query. This potential inaccuracy is estimated to be less than 1%. This inaccuracy can occur because a document_id may be counted twice when it continues across more than one row in the index fragment, or when it appears more than once in the same row. To obtain a more accurate count for a specific document, use sys.dm_fts_index_keywords_by_document or a CONTAINS query.

Requires membership in the sysadmin fixed server role.

A. Displaying high-level full-text index content

SELECT * FROM sys.dm_fts_index_keywords(db_id('AdventureWorks'), object_id('HumanResources.JobCandidate'))
GO

Reference

Concepts