sp_fulltext_column

Specifies whether or not a particular column of a table participates in full-text indexing.

Syntax

sp_fulltext_column [ @tabname = ] 'qualified_table_name' ,
    [ @colname = ] 'column_name' ,
    [ @action = ] 'action'
    [ , [ @language = ] 'language' ]
    [ , [ @type_colname = ] 'type_column_name' ]

Arguments

[@tabname =] 'qualified_table_name'

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

[@colname =] 'column_name'

Is the name of a column in qualified_table_name. The column must be either a character or an image column and cannot be a computed column. column_name is sysname, with no default.

Note  SQL Server can create full-text indexes of text data stored in columns that are of image data type. Images or pictures are not indexed.

[@action =] 'action'

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

[ @language = ] 'language'

Is the language of the data stored in the column. The following table lists languages included in SQL Server.

Note  Use 'Neutral' when a column contains data in multiple languages or in an unsupported language. The default is specified by the configuration option 'default full-text language'.

[@type_colname =] 'type_column_name'

Is the name of a column in qualified_table_name that holds the document type of column_name. This column must be char, nchar, varchar, or nvarchar. It is only used when the data type of column_name is an image. type_column_name is sysname, with no default.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

If the full-text index is active, any ongoing population is stopped. Furthermore, if a table with an active full-text index has change tracking enabled, SQL server ensures that the index is current. For example, SQL Server stops any current population on the table, drops the existing index, and starts a new population.

If change tracking is on and columns need to be added or dropped from the full-text index while preserving the index, the table should be deactivated, and the required columns should be added or dropped. These actions freeze the index. The table can be activated later when starting a population is practical.

Permissions

Only members of the sysadmin fixed server role, db_owner and db_ddladmin fixed database roles, and the object owner can execute sp_fulltext_column.

Examples

Adding a column to a full-text index

1. This example adds the Description column from the Categories table to the table's full-text index.

   USE Northwind
   EXEC sp_fulltext_column Categories, Description, 'add'
   

2. This example assumes you created a full-text index on spanishTbl table. To add the spanishCol column:

   sp_fulltext_column 'spanishTbl', 'spanishCol', 'add', 0xC0A
   

   When you run this query:

   SELECT * 
   FROM spanishTbl 
   WHERE CONTAINS(spanishCol, 'formsof(inflectional, trabajar)')
   

   Your result set would include rows with different forms of trabajar(to work), such as trabajo, trabajamos, and trabajan.

   Note  All columns listed in a single full-text query function clause must use the same language.

See Also

OBJECTPROPERTY

sp_help_fulltext_columns

sp_help_fulltext_columns_cursor

sp_help_fulltext_tables

sp_help_fulltext_tables_cursor

System Stored Procedures