Full-text Query Transact-SQL Components

Microsoft® SQL Server™ provides these Transact-SQL components for full-text querying.

Transact-SQL predicates

These predicates can be used in any search condition (including a WHERE clause) of a SELECT statement.

• CONTAINS
  
  
• FREETEXT

Transact-SQL rowset-valued functions

These functions can be used in the FROM clause of a SELECT statement.

• CONTAINSTABLE
  
  
• FREETEXTTABLE

Transact-SQL full-text properties

These Transact-SQL functions return information about the full-text properties of database objects.

SERVERPROPERTY

• IsFullTextInstalled indicates that the full-text component is installed with the current instance of SQL Server. This property is the counterpart of the FULLTEXTSERVICEPROPERTY function property with the same name.

DATABASEPROPERTYEX and DATABASEPROPERTY

• IsFullTextEnabled indicates whether a database has been enabled for full-text indexing.

COLUMNPROPERTY

• IsFullTextIndexed indicates whether a column has been enabled for full-text indexing.

OBJECTPROPERTY

• TableFullTextBackgroundUpdateIndexOn indicates whether a table has full-text background update indexing.
  
  
• TableFullTextCatalogId provides the full-text catalog ID in which the full-text index data for the table resides.
  
  
• TableFullTextChangeTrackingOn indicates whether full-text change-tracking is enabled on the table.
  
  
• TableFullTextKeyColumn provides the column ID of the full-text unique key column.
  
  
• TableFullTextPopulateStatus indicate the population status of a full-text table.
  
  
• TableHasActiveFulltextIndex indicates whether a table has an active full-text index.

INDEXPROPERTY

• IsFulltextKey indicates whether the index is the full-text key for a table.

Transact-SQL has functions that specifically return full-text properties.

FULLTEXTCATALOGPROPERTY returns information about full-text catalog properties: PopulateStatus, ItemCount, IndexSize, UniqueKeyCount, LogSize, and PopulateCompletionAge. For more information, see FULLTEXTCATALOGPROPERTY.

FULLTEXTSERVICEPROPERTY returns information about the full-text service-level properties: ResourceUsage, ConnectTimeout, and IsFulltextInstalled. IsFulltextInstalled returns the same information as the SERVERPROPERTY property of the same name. For more information, see FULLTEXTSERVICEPROPERTY.

Transact-SQL full-text system stored procedures

These stored procedures can be used in conjunction with writing a query. For example, you can use them to find the names of the full-text indexed columns for a table and the column ID of a full-text unique key column before specifying a query.

• sp_fulltext_database is a stored procedure that enables or removes full-text indexing from the current database.
  
  
• sp_fulltext_catalog, sp_fulltext_table, and sp_fulltext_column are stored procedures used in defining full-text indexes and initiating full-text index population.
  
  
• sp_help_fulltext_catalogs, sp_help_fulltext_tables, sp_help_fulltext_columns, and a variation of these stored procedures are used to query the full-text index meta data defined by the system stored procedures identified earlier.

Note  The full-text system stored procedures cannot be used in a transaction.