Transact-SQL Reference

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.

Value	Description
add	Adds the column_name of the qualified_table_name to the table's inactive full-text index. This action enables the column for full-text indexing.
drop	Removes column_name of qualified_table_name from the table's inactive full-text index.

[ @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'.

Locale	Locale ID
Neutral	0
Chinese_Simplified	0x0804
Chinese_Traditional	0x0404
Dutch	0x0413
English_UK	0x0809
English_US	0x0409
French	0x040c
German	0x0407
Italian	0x0410
Japanese	0x0411
Korean	0x0412
Spanish_Modern	0x0c0a
Swedish_Default	0x041d

[@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

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'
```
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.