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_by_document 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_by_document is useful for finding out the following, among other things:

• The total number of keywords that a full-text index contains.
  
• Whether a keyword is part of a given document or row.
  
• How many times a keyword appears in the whole full-text index; that is:
  ( SUM ( occurrence_count ) WHERE keyword=keyword_value )
  
• How many times a keyword appears in a given document or row.
  
• How many keywords a given document or row contains.
  

Also, you can also use the information provided by sys.dm_fts_index_keywords_by_document to retrieve all the keywords belonging to a given document or row.

When the full-text key column is an integer data type, as recommended, the document_id maps directly to the full-text key value in the base table.

In contrast, when the full-text key column uses a non-integer data type, document_id does not represent the full-text key in the base table. In this case, to identify the row in the base table that is returned by dm_fts_index_keywords_by_document, you need to join this view with the results returned by sp_fulltext_keymappings. Before you can join them, you must store the output of the stored procedure in a temp table. Then you can join the document_id column of dm_fts_index_keywords_by_document with the DocId column that is returned by this stored procedure. Note that a timestamp column cannot receive values at insert time, because they are auto-generated by SQL Server. Therefore, the timestamp column must be converted to varbinary(8) columns. The following example shows these steps. In this example, table_id is the ID of your table, database_name is the name of your database, and table_name is the name of your table.

USE database_name;
GO
CREATE TABLE #MyTempTable 
   (
      docid INT PRIMARY KEY ,
      [key] INT NOT NULL
   );
DECLARE @db_id int = db_id(N'database_name');
DECLARE @table_id int = OBJECT_ID(N'table_name');
INSERT INTO #MyTempTable EXEC sp_fulltext_keymappings @table_id;
SELECT * FROM sys.dm_fts_index_keywords_by_document 
   ( @db_id, @table_id ) kbd
   INNER JOIN #MyTempTable tt ON tt.[docid]=kbd.document_id;
GO

Requires CREATE FULLTEXT CATALOG permissions and SELECT permission on the columns covered by the full-text index.

A. Displaying full-text index content at the document level

The following example displays the content of the full-text index at the document level in the HumanResources.JobCandidate table of the AdventureWorks sample database.

Note:
You can create this index by executing the example provided for the HumanResources.JobCandidate table in CREATE FULLTEXT INDEX (Transact-SQL).

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

Reference

Other Resources

Help and Information