sp_fulltext_catalog (T-SQL)

Creates and drops a full-text catalog, and starts and stops the indexing action for a catalog. Multiple full-text catalogs can be created per database.

Syntax

sp_fulltext_catalog  [@ftcat =] 'fulltext_catalog_name',
    [@action =] 'action'
    [,[@path =] 'root_directory']

Arguments

[@ftcat =] 'fulltext_catalog_name'

Is the name of the full-text catalog. Catalog names must be unique for each database. fulltext_catalog_name is sysname.

[@action =] 'action'

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

 

Value	Description
create	Creates an empty, new full-text catalog in the file system and adds an associated row in sysfulltextcatalogs with the fulltext_catalog_name and root_directory (if present) values. fulltext_catalog_name must be unique within the database.
drop	Drops fulltext_catalog_name by removing it from the file system and deleting the associated row in sysfulltextcatalogs. This action fails if full-text index metadata has been created for the catalog; that is, if sp_fulltext_table with the create option has been executed for this catalog. An error is displayed if the catalog does not exist.
start_incremental	Starts an incremental population for fulltext_catalog_name. An error is displayed if the catalog does not exist. If a full-text index population is already active, a warning is displayed but no population action occurs. With incremental population, only changed rows are retrieved for full-text indexing, provided there is a timestamp column present in the table being full-text indexed.
start_full	Starts a full population for fulltext_catalog_name. Every row of every table associated with this full-text catalog is retrieved for full-text indexing, even if it already has been indexed.
stop	Stops an index population for fulltext_catalog_name. An error is displayed if the catalog does not exist. No warning is displayed if population is already stopped.
rebuild	Rebuilds fulltext_catalog_name by deleting the existing full-text catalog from the file system, re-creating the full-text catalog, and reassociating the full-text catalog with all the tables that have full-text indexing references. Rebuilding does not change any full-text metadata in the database system tables, nor does it cause the repopulation of the newly created full-text catalog. To repopulate, sp_fulltext_catalog must be executed with the start_full or start_incremental action.

[@path =] 'root_directory'

Is the root directory (not the complete physical path) for a create action. root_directory is nvarchar(100) and has a default value of NULL, which indicates to use the default location specified at setup. (This is the Ftdata subdirectory in the Mssql7 directory; for example, C:\Mssql7\Ftdata.). The specified root directory must exist, reside on a drive on the same computer; consist of more than just the drive letter, and cannot be a relative path. Network drives, removable drives, floppy disks, and UNC paths are not supported. Full-text catalogs must be created on a local hard drive associated with the machine on which SQL Server is running.

@path is valid only when action is create. For actions other than create (stop, rebuild, and so on), @path must be NULL or omitted.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

The start_full action is used to create a complete snapshot of the full-text data in fulltext_catalog_name. The start_incremental action is used to reindex only the changed rows in the database. For an incremental index, a timestamp column is required in one column of the table.

Full-text catalog and index data is stored in files created in a full-text catalog directory. The full-text catalog directory is created as a sub-directory of the directory specified in @path, or in the server's default full-text catalog directory if @path is not specified. The name of the full-text catalog directory is built in a way that guarantees it will be unique on the server. Therefore, all full-text catalog directories on a server can share the same path.

Permissions

Only members of the db_owner (or higher) fixed database roles can execute sp_fulltext_catalog.

Examples

A. Create a full-text catalog

This example creates an empty full-text catalog, Cat_Desc, in the Northwind database.

USE Northwind

EXEC sp_fulltext_catalog 'Cat_Desc', 'create'

B. Start the population of a full-text catalog

This example begins a full population of the Cat_Desc catalog.

USE Northwind

EXEC sp_fulltext_catalog 'Cat_Desc', 'start_full'

See Also