Full-text Query Transact-SQL Components

Accessing and Changing Relational Data

Accessing and Changing Relational Data

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.


Transact-SQL rowset-valued functions

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


Transact-SQL full-text properties

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


  • 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.


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


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


  • 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.


  • 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.