Full-Text Search and Semantic Search Catalog Views

Se more view version maps here: Microsoft Sql System View Version Maps

• sys.fulltext_index_columns
• sys.fulltext_index_fragments
• sys.fulltext_indexes
• sys.fulltext_languages

sys.fulltext_index_columns

column that is part of a full-text index.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
object_id	•	•	•	•	int	ID of the object of which this is part.
column_id	•	•	•	•	int	ID of the column that is part of the full-text index.
type_column_id	•	•	•	•	int	ID of the column that acts as the type specifier for full-text indexing. NULL if not applicable.
language_id	•	•	•	•	int	LCID used to index this full-text column. 0 = Neutral.
statistical_semantics				•	int	1 = This column has statistical semantics enabled in addition to full-text indexing.

TSQL

Sql 2005
SELECT [object_id], [column_id], [type_column_id], [language_id] FROM sys.fulltext_index_columns
Sql 2008
SELECT [object_id], [column_id], [type_column_id], [language_id] FROM sys.fulltext_index_columns
Sql 2008 R2
SELECT [object_id], [column_id], [type_column_id], [language_id] FROM sys.fulltext_index_columns
Sql 2012
SELECT [object_id], [column_id], [type_column_id], [language_id], [statistical_semantics] FROM sys.fulltext_index_columns

Back to Top

---

sys.fulltext_index_fragments

A fulltext index uses internal tables called full-text index fragments to store the inverted index data. This view can be used to query the metadata about these fragments. This view contains a row for each full-text index fragment in every table that contains a full-text index.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
table_id		•	•	•	int	Object ID of the table that contains the full-text index fragment.
fragment_object_id		•	•	•	int	Object ID of the internal table associated with the fragment.
fragment_id		•	•	•	int	Logical ID of the full-text index fragment. This is unique across all fragments for this table.
timestamp		•	•	•	timestamp	Timestamp associated with the fragment creation. The timestamps of more recent fragments are larger than the timestamps of older fragments.
data_size		•	•	•	int	Logical size of the fragment in bytes.
row_count		•	•	•	int	Number of individual rows in the fragment.
status		•	•	•	int	Status of the fragment, one of: 0 = Newly created and not yet used 1 = Being used for insert during fulltext index population or merge 4 = Closed. Ready for query 6 = Being used for merge input and ready for query 8 = Marked for deletion. Will not be used for query and merge source. A status of 4 or 6 means that the fragment is part of the logical full-text index and can be queried; that is, it is a queryable fragment.

TSQL

Sql 2008
SELECT [table_id], [fragment_object_id], [fragment_id], [timestamp], [data_size], [row_count], [status] FROM sys.fulltext_index_fragments
Sql 2008 R2
SELECT [table_id], [fragment_object_id], [fragment_id], [timestamp], [data_size], [row_count], [status] FROM sys.fulltext_index_fragments
Sql 2012
SELECT [table_id], [fragment_object_id], [fragment_id], [timestamp], [data_size], [row_count], [status] FROM sys.fulltext_index_fragments

Back to Top

---

sys.fulltext_indexes

Contains a row per full-text index of a tabular object.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
object_id	•	•	•	•	int	ID of the object to which this full-text index belongs.
unique_index_id	•	•	•	•	int	ID of the corresponding unique, non-full-text index that is used to relate the full-text index to the rows.
fulltext_catalog_id	•	•	•	•	int	ID of the full-text catalog in which the full-text index resides.
is_enabled	•	•	•	•	bit	1 = Full-text index is currently enabled.
change_tracking_state	•	•	•	•	char(1)	State of change-tracking. M = Manual A = Auto O = Off
change_tracking_state_desc	•	•	•	•	nvarchar(60)	Description of the state of change-tracking. MANUAL AUTO OFF
has_crawl_completed	•	•	•	•	bit	Last crawl (population) that the full-text index has completed.
crawl_type	•	•	•	•	char(1)	Type of the current or last crawl. F = Full crawl I = Incremental, timestamp-based crawl U = Update crawl, based on notifications P = Full crawl is paused.
crawl_type_desc	•	•	•	•	nvarchar(60)	Description of the current or last crawl type. FULL_CRAWL INCREMENTAL_CRAWL UPDATE_CRAWL PAUSED_FULL_CRAWL
crawl_start_date	•	•	•	•	datetime	Start of the current or last crawl. NULL = None.
crawl_end_date	•	•	•	•	datetime	End of the current or last crawl. NULL = None.
incremental_timestamp	•	•	•	•	binary(8)	Timestamp value to use for the next incremental crawl. NULL = None.
stoplist_id		•	•	•	int	ID of the stoplist1 that is associated with this full-text index.
data_space_id		•	•	•	int	Filegroup where this full-text index resides.
property_list_id				•	int	ID of the search property list that is associated with this full-text index. NULL indicates that no search property list is associated with the full-text index. To obtain more information about this search property list, use the sys.registered_search_property_lists (Transact-SQL)2 catalog view.

TSQL

Sql 2005
SELECT [object_id], [unique_index_id], [fulltext_catalog_id], [is_enabled], [change_tracking_state], [change_tracking_state_desc], [has_crawl_completed], [crawl_type], [crawl_type_desc], [crawl_start_date], [crawl_end_date], [incremental_timestamp] FROM sys.fulltext_indexes
Sql 2008
SELECT [object_id], [unique_index_id], [fulltext_catalog_id], [is_enabled], [change_tracking_state], [change_tracking_state_desc], [has_crawl_completed], [crawl_type], [crawl_type_desc], [crawl_start_date], [crawl_end_date], [incremental_timestamp], [stoplist_id], [data_space_id] FROM sys.fulltext_indexes
Sql 2008 R2
SELECT [object_id], [unique_index_id], [fulltext_catalog_id], [is_enabled], [change_tracking_state], [change_tracking_state_desc], [has_crawl_completed], [crawl_type], [crawl_type_desc], [crawl_start_date], [crawl_end_date], [incremental_timestamp], [stoplist_id], [data_space_id] FROM sys.fulltext_indexes
Sql 2012
SELECT [object_id], [unique_index_id], [fulltext_catalog_id], [is_enabled], [change_tracking_state], [change_tracking_state_desc], [has_crawl_completed], [crawl_type], [crawl_type_desc], [crawl_start_date], [crawl_end_date], [incremental_timestamp], [stoplist_id], [data_space_id], [property_list_id] FROM sys.fulltext_indexes

Back to Top

---

sys.fulltext_languages

This catalog view contains one row per language whose word breakers are registered with SQL Server. Each row displays the LCID and name of the language. When word breakers are registered for a language, its other linguistic resources€”stemmers, noise words (stopwords), and thesaurus files€”become available to full-text indexing/querying operations. The value of name or lcid can be specified in the full-text queries and full-text index Transact-SQL statements. ColumnData typeDescription lcid int Microsoft Windows locale identifier (LCID) for the language. name sysname Is either the value of the alias in sys.syslanguages1 corresponding to the value of lcid or the string representation of the numeric LCID.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
lcid	•	•	•	•	int	Microsoft Windows local ID for the language.
name	•	•	•	•	sysname	Is either the value of the alias in syslanguages corresponding to the value of lcid, or is the string representation of the numeric lcid.

TSQL

Sql 2005
SELECT [lcid], [name] FROM sys.fulltext_languages
Sql 2008
SELECT [lcid], [name] FROM sys.fulltext_languages
Sql 2008 R2
SELECT [lcid], [name] FROM sys.fulltext_languages
Sql 2012
SELECT [lcid], [name] FROM sys.fulltext_languages

Back to Top

---