I have a requirement from a customer to build a query that searches all columns of a table using a Full text index for a specific phrase. That part is no problem. But, then the user also wants to be able to return other high-frequency words that are found in the same row. For example, a row may contain the sentence "The quick brown fox jumped over the lazy brown dog." The user searches for "fox." I will pass that into a query using the "contains" clause, and the row will be returned. Lets say Dog, quick, and lazy are also common words in the table. The user would like to see a return that shows something like: "The row also contains 'Dog, 'quick,' and 'lazy'." Is there a good method to scan the row returned for high frequency words, and then return those as well?
Depends which version of SQL Server, with 2008+ check: sys.dm_fts_index_keywords( DB_ID('database_name'), OBJECT_ID('table_name') ) - display_term,document_count sys.dm_fts_index_keywords_by_document (DB_ID('database_name'),OBJECT_ID('table_name')) - display_term,document_id,occurrence_count