TechNet
Export (0) Print
Expand All

sp_fulltext_column (Transact-SQL)

 

Updated: June 10, 2016

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

System_CAPS_ICON_important.jpg Important


This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER FULLTEXT INDEX instead.

Applies to: SQL Server (SQL Server 2008 through current version), SQL Database V12.

Topic link icon Transact-SQL Syntax Conventions

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

[ @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, varbinary(max) or image column and cannot be a computed column. column_name is sysname, with no default.

System_CAPS_ICON_note.jpg Note


SQL Server can create full-text indexes of text data stored in columns that are of varbinary(max) or image data type. Images and 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 the following values.

ValueDescription
addAdds column_name of qualified_table_name to the table's inactive full-text index. This action enables the column for full-text indexing.
dropRemoves column_name of qualified_table_name from the table's inactive full-text index.

[ @language= ] 'language_term'
Is the language of the data stored in the column. For a list of languages included in SQL Server, see sys.fulltext_languages (Transact-SQL).

System_CAPS_ICON_note.jpg 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 of type varbinary(max) or image. type_column_name is sysname, with no default.

0 (success) or 1 (failure)

None

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.

User must be a member of the db_ddladmin fixed database role, or a member of the db_owner fixed database role, or the owner of the table.

The following example adds the DocumentSummary column from the Document table to the full-text index of the table.

USE AdventureWorks2012;  
GO  
EXEC sp_fulltext_column 'Production.Document', DocumentSummary, 'add';  
GO  

The following example assumes you created a full-text index on a table named spanishTbl. To add the spanishCol column to the full-text index, execute the following stored procedure:

EXEC sp_fulltext_column 'spanishTbl', 'spanishCol', 'add', 0xC0A;  
GO  

When you run this query:

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

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

System_CAPS_ICON_note.jpg Note


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

OBJECTPROPERTY (Transact-SQL)
sp_help_fulltext_columns (Transact-SQL)
sp_help_fulltext_columns_cursor (Transact-SQL)
sp_help_fulltext_tables (Transact-SQL)
sp_help_fulltext_tables_cursor (Transact-SQL)
System Stored Procedures (Transact-SQL)
Full-Text Search and Semantic Search Stored Procedures (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft