sp_fulltext_table (T-SQL)

Marks or unmarks a table for full-text indexing.

Syntax

sp_fulltext_table [@tabname =] 'qualified_table_name',
    [@action =] 'action'
    [,[@ftcat =] 'fulltext_catalog_name',
    [@keyname =] 'unique_index_name']

Arguments

[@tabname =] 'qualified_table_name'

Is a one- or two-part table name. The table must exist in the current database. qualified_table_name is nvarchar(517), with no default.

[@action =] 'action'

Is the action to be performed. action is varchar(20), with no default, and can be one of these values.

 

Value	Description
create	Creates the metadata for a full-text index for the table referenced by qualified_table_name and specifies that the full-text index data for this table should reside in fulltext_catalog_name. This action also designates the use of unique_index_name as the full-text key column. This unique index must already be present and must be defined on one column of the table. A full-text search cannot be performed against this table until the full-text index has been activated and the full-text catalog has been populated using either the start_incremental or start_full action of sp_fulltext_catalog.
drop	Drops the metadata on the full-text index for qualified_table_name. If the full-text index is active, it is automatically deactivated before being dropped. It is not necessary to remove columns before dropping the full-text index.
activate	Activates the ability for full-text index data to be gathered for qualified_table_name. There must be at least one column participating in the full-text index before it can be activated. Note that this does not actually populate the full-text index, but simply registers the table in the full-text catalog in the file system so that rows from qualified_table_name can be retrieved during the next full-text index population.
deactivate	Deactivates the full-text index for qualified_table_name so that it no longer participates in the population of the full-text catalog. The full-text index metadata remains, however, and the table can be reactivated.

[@ftcat =] 'fulltext_catalog_name'

Is a valid, existing full-text catalog name for a create action. For all other actions, this parameter must be NULL. fulltext_catalog_name is sysname, with a default of NULL.

[@keyname =] 'unique_index_name'

Is a valid single-key-column, unique nonnullable index on qualified_table_name for a create action. For all other actions, this parameter must be NULL. unique_index_name is sysname, with a default of NULL.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

After a full-text index has been deactivated for a particular table, the existing full-text index remains in place until the next full population; however, this index is not used because Microsoft® SQL Server™ blocks queries on deactivated tables.

If the table is reactivated and the index is not repopulated, the old index is still available for queries against any remaining (but not new) full-text enabled columns. Data from deleted columns are matched in queries that specify an all-full-text columns (*) search.

After a table has been defined for full-text indexing, switching the full-text unique key column from one data type to another (either by changing the data type of that column or changing the full-text unique key from one column to another) without a full repopulation may cause a failure to occur during a subsequent query (returning the error message: “Conversion to type data_type failed for full-text search key value key_value”). To prevent this, drop the full-text definition for this table altogether (using the drop action of sp_fulltext_table) and redefine it (using sp_fulltext_table and sp_fulltext_column).

If the full-text unique key column is a character or Unicode character column, it must be defined to be 450 bytes or less.

Permissions

Only members of the db_owner and db_ddladmin fixed database roles can execute sp_fulltext_table.

Examples

A. Create metadata for a full-text index on a table

This example creates full-text index metadata for the Categories table of the Northwind database. Cat_Desc is a full-text catalog. PK_Categories is a unique, single-column index on Categories.

USE Northwind

EXEC sp_fulltext_table Categories, 'create', 'Cat_Desc', PK_Categories

B. Activate full-text indexing on a table

This example activates full-text indexing on the Categories table.

USE Northwind

EXEC sp_fulltext_table Categories, 'activate'

See Also