sys.dm_fts_index_keywords (Transact-SQL)
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
To view lower-level full-text index information, use the sys.dm_fts_index_keywords_by_document dynamic management function at the document level.
Syntax
sys.dm_fts_index_keywords( DB_ID('database_name'), OBJECT_ID('table_name'))
Arguments
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.
Table Returned
Column name |
Data type |
Description |
|---|---|---|
keyword |
nvarchar(8000) |
The hexadecimal representation of the keyword stored inside the full-text index. .gif "Note") Note
OxFF represents the special character that indicates the end of a file or dataset.
|
display_term |
nvarchar(8000) |
The human-readable format of the keyword. This format is derived from the hexadecimal format. 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. |
Remarks
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.
Permissions
Requires CREATE FULLTEXT CATALOG permissions and SELECT permission on the columns covered by the full-text index.
Examples
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 of the AdventureWorks sample database. .
SELECT * FROM sys.dm_fts_index_keywords(db_id('AdventureWorks'), object_id('HumanResources.JobCandidate'))
GO