This topic has not yet been rated - Rate this topic

sys.dm_fts_index_keywords (Transact-SQL)

SQL Server 2012

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

Reference

Full-Text Search Dynamic Management Views and Functions (Transact-SQL)

sys.dm_fts_index_keywords_by_document (Transact-SQL)

Concepts

Full-Text Search (SQL Server)

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD