December 6, 2012

Full-Text Search and Semantic Search Catalog Views

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

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

No comments:

Post a Comment

Total Pageviews