Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

sys.dm_fts_index_keywords (Transact-SQL)

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Returns information about the content of a full-text index for the specified table.

sys.dm_fts_index_keywords is a dynamic management function.

Note Note

To view lower-level full-text index information, use the sys.dm_fts_index_keywords_by_document dynamic management function at the document level.

Applies to: SQL Server (SQL Server 2008 through current version).

sys.dm_fts_index_keywords( DB_ID('database_name'), OBJECT_ID('table_name') )

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.

Column name

Data type

Description

keyword

nvarchar(4000)

The hexadecimal representation of the keyword stored inside the full-text index.

Note Note

OxFF represents the special character that indicates the end of a file or dataset.

display_term

nvarchar(4000)

The human-readable format of the keyword. This format is derived from the hexadecimal format.

Note Note

The display_term value for OxFF is "END OF FILE."

column_id

int

ID of the column from which the current keyword was full-text indexed.

document_count

int

Number of documents or rows containing the current term.

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

The following example displays information about the high-level content of the full-text index in the HumanResources.JobCandidate table.

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

Community Additions

ADD
Show:
© 2015 Microsoft