Full-Text Search Catalog Views

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

• sys.fulltext_catalogs
• sys.fulltext_document_types
• sys.fulltext_index_catalog_usages
• sys.fulltext_stoplists
• sys.fulltext_stopwords
• sys.fulltext_system_stopwords
• sys.registered_search_properties
• sys.registered_search_property_lists

sys.fulltext_catalogs

full-text catalog. Note The following columns will be removed in a future release of SQL Server: data_space_id, file_id, and path. Do not use these columns in new development work, and modify applications that currently use any of these columns as soon as possible.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
fulltext_catalog_id	•	•	•	•	int	ID of the full-text catalog. Is unique across the full-text catalogs in the database.
name	•	•	•	•	sysname	Name of the catalog. Is unique within the database.
path	•	•	•	•	nvarchar(260)	Name of the catalog directory in the file system.
is_default	•	•	•	•	bit	The default full-text catalog. True = Is default. False = Is not default.
is_accent_sensitivity_on	•	•	•	•	bit	Accent-sensitivity setting of the catalog. True = Is accent-sensitive. False = Is not accent-sensitive.
data_space_id	•	•	•	•	int	Filegroup where this catalog was created.
file_id	•	•	•	•	int	File ID of the full-text file associated with the catalog.
principal_id	•	•	•	•	int	ID of the database principal that owns the full-text catalog.
is_importing		•	•	•	bit	Indicates whether the full-text catalog is being imported: 1 = The catalog is being imported. 2 = The catalog is not being imported.

TSQL

Sql 2005
SELECT [fulltext_catalog_id], [name], [path], [is_default], [is_accent_sensitivity_on], [data_space_id], [file_id], [principal_id] FROM sys.fulltext_catalogs
Sql 2008
SELECT [fulltext_catalog_id], [name], [path], [is_default], [is_accent_sensitivity_on], [data_space_id], [file_id], [principal_id], [is_importing] FROM sys.fulltext_catalogs
Sql 2008 R2
SELECT [fulltext_catalog_id], [name], [path], [is_default], [is_accent_sensitivity_on], [data_space_id], [file_id], [principal_id], [is_importing] FROM sys.fulltext_catalogs
Sql 2012
SELECT [fulltext_catalog_id], [name], [path], [is_default], [is_accent_sensitivity_on], [data_space_id], [file_id], [principal_id], [is_importing] FROM sys.fulltext_catalogs

Back to Top

---

sys.fulltext_document_types

document type that is available for full-text indexing operations. Each row represents the IFilter interface that is registered in the instance of SQL Server.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
document_type	•	•	•	•	sysname	The file extension of the supported document type. This value can be used to identify the filter that will be used during full-text indexing of columns of type varbinary(max) or image.
class_id	•	•	•	•	uniqueidentifier	GUID of the IFilter class that supports file extension.
path	•	•	•	•	nvarchar(260)	The path to the IFilter DLL. The path is only visible to members of the serveradmin fixed server role.
version	•	•	•	•	sysname	Version of the IFilter DLL.
manufacturer	•	•	•	•	sysname	Name of the IFilter manufacturer.

TSQL

Sql 2005
SELECT [document_type], [class_id], [path], [version], [manufacturer] FROM sys.fulltext_document_types
Sql 2008
SELECT [document_type], [class_id], [path], [version], [manufacturer] FROM sys.fulltext_document_types
Sql 2008 R2
SELECT [document_type], [class_id], [path], [version], [manufacturer] FROM sys.fulltext_document_types
Sql 2012
SELECT [document_type], [class_id], [path], [version], [manufacturer] FROM sys.fulltext_document_types

Back to Top

---

sys.fulltext_index_catalog_usages

full-text catalog to full-text index reference.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
object_id	•	•	•	•	int	ID of the full-text indexed table. Is unique within the database.
index_id	•	•	•	•	int	ID of full-text index.
fulltext_catalog_id	•	•	•	•	int	ID of full-text catalog.

TSQL

Sql 2005
SELECT [object_id], [index_id], [fulltext_catalog_id] FROM sys.fulltext_index_catalog_usages
Sql 2008
SELECT [object_id], [index_id], [fulltext_catalog_id] FROM sys.fulltext_index_catalog_usages
Sql 2008 R2
SELECT [object_id], [index_id], [fulltext_catalog_id] FROM sys.fulltext_index_catalog_usages
Sql 2012
SELECT [object_id], [index_id], [fulltext_catalog_id] FROM sys.fulltext_index_catalog_usages

Back to Top

---

sys.fulltext_stoplists

Contains a row per full-text stoplist in the database.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
stoplist_id		•	•	•	int	ID of the stoplist, unique within the database.
name		•	•	•	sysname	Name of the stoplist.
create_date		•	•	•	datetime	Date that stoplist was created.
modify_date		•	•	•	datetime	Date that stoplist was last modified using any ALTER statement.
principal_id		•	•	•	int	ID of the database principal that owns the stoplist.

TSQL

Sql 2008
SELECT [stoplist_id], [name], [create_date], [modify_date], [principal_id] FROM sys.fulltext_stoplists
Sql 2008 R2
SELECT [stoplist_id], [name], [create_date], [modify_date], [principal_id] FROM sys.fulltext_stoplists
Sql 2012
SELECT [stoplist_id], [name], [create_date], [modify_date], [principal_id] FROM sys.fulltext_stoplists

Back to Top

---

sys.fulltext_stopwords

Contains a row per stopword for all stoplists in the database.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
stoplist_id		•	•	•	int	ID of the stoplist to which stopword belongs. This ID is unique within the database.
stopword		•	•	•	nvarchar(64)	The term to be considered for a stop-word match.
language		•	•	•	sysname	Is either the value of the alias in sys.fulltext_languages1 corresponding to the value of the locale identifier (LCID), or is the string representation of the numeric LCID.
language_id		•	•	•	int	LCID used for word breaking.

TSQL

Sql 2008
SELECT [stoplist_id], [stopword], [language], [language_id] FROM sys.fulltext_stopwords
Sql 2008 R2
SELECT [stoplist_id], [stopword], [language], [language_id] FROM sys.fulltext_stopwords
Sql 2012
SELECT [stoplist_id], [stopword], [language], [language_id] FROM sys.fulltext_stopwords

Back to Top

---

sys.fulltext_system_stopwords

Provides access to the system stoplist.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
stopword		•	•	•	nvarchar(64)	The term that is considered for a stop-word match.
language_id		•	•	•	int	Locale identifier (LCID) of the language. This LCID is used for word breaking.

TSQL

Sql 2008
SELECT [stopword], [language_id] FROM sys.fulltext_system_stopwords
Sql 2008 R2
SELECT [stopword], [language_id] FROM sys.fulltext_system_stopwords
Sql 2012
SELECT [stopword], [language_id] FROM sys.fulltext_system_stopwords

Back to Top

---

sys.registered_search_properties

search property contained by any search property list on the current database.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
property_list_id				•	int	ID of the search property list to which this property belongs.
property_set_guid				•	uniqueidentifier	Globally unique identifier GUID that identifies the property set to which the search property belongs.
property_int_id				•	int	Integer that identifies this search property within the property set. property_int_id is unique within the property set.
property_name				•	nvarchar(64)	Name that uniquely identifies this search property in the search property list. Note To search on a property, specify this property name in the CONTAINS1 predicate.
property_description				•	nvarchar(512)	Description of the property.
property_id				•	int	Internal property ID of the search property within the search property list identified by the property_list_id value. When a given property is added to a given search property list, the Full-Text Engine registers the property and assigns it an internal property ID that is specific to that property list. The internal property ID, which is an integer, is unique to a given search property list. If a given property is registered for multiple search property lists, a different internal property ID might be assigned for each search property list. Note The internal property ID is distinct from the property integer identifier that is specified when adding the property to the search property list. For more information, see Search Document Properties with Search Property Lists2. To view all property-related content in the full-text index sys.dm_fts_index_keywords_by_property (Transact-SQL)3

TSQL

Sql 2012
SELECT [property_list_id], [property_set_guid], [property_int_id], [property_name], [property_description], [property_id] FROM sys.registered_search_properties

Back to Top

---

sys.registered_search_property_lists

search property list on the current database.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
property_list_id				•	int	ID of the property list.
name				•	sysname	Name of the property list.
create_date				•	datetime	Date the property list was created.
modify_date				•	datetime	Date the property list was last modified by any ALTER statement.
principal_id				•	int	Owner of the property list.

TSQL

Sql 2012
SELECT [property_list_id], [name], [create_date], [modify_date], [principal_id] FROM sys.registered_search_property_lists

Back to Top

---