sp_fulltext_column

Transact-SQL Reference

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