Full-text Query Transact-SQL Components

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

• Transact-SQL predicates:
  • CONTAINS
  • FREETEXT

  The CONTAINS and FREETEXT predicates can be used in any search condition (including a WHERE clause) of a SELECT statement.

• Transact-SQL rowset-valued functions:
  • CONTAINSTABLE
  • FREETEXTTABLE

  The CONTAINSTABLE and FREETEXTTABLE functions can be used in the FROM clause of a SELECT statement.

• Transact-SQL full-text properties:

  These are some of the properties that are used with full-text queries and the functions that are used to obtain them:

  • IsFullTextEnabled property indicates whether a database has been enabled for full-text and is available through the DatabaseProperty function.
  • TableHasActiveFulltextIndex property indicates whether a table has been enabled for full-text and is available through the ObjectProperty function.
  • IsFullTextIndexed property indicates whether a column has been enabled for full-text and is available through the ColumnProperty function.
  • TableFullTextKeyColumn property provides the column ID of the full-text unique key column and is available through the ObjectProperty function.
• Transact-SQL full-text system stored procedures:
  • Stored procedures that define full-text indexes and initiate full-text index population, such as sp_fulltext_catalog, sp_fulltext_table, and sp_fulltext_column.
  • Stored procedures that query the full-text index metadata that have been defined by the above system stored procedures, such as sp_help_fulltext_catalogs, sp_help_fulltext_tables, sp_help_fulltext_columns, and a variation of these that allow cursors over the result sets returned.

  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.