Creates and drops a full-text catalog, and starts and stops the indexing action for a catalog. Multiple full-text catalogs can be created for each database.
|Applies to: SQL Server (SQL Server 2008 through current version), SQL Database V12.|
sp_fulltext_catalog [ @ftcat= ] 'fulltext_catalog_name' , [ @action= ] 'action' [ , [ @path= ] 'root_directory' ]
[ @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.
|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 this catalog contains indexes for one or more tables. sp_fulltext_table 'table_name', 'drop' should be executed to drop the tables from the 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 they have already 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. When a catalog is rebuilt, the existing catalog is deleted and a new catalog is created in its place. All the tables that have full-text indexing references are associated with the new catalog. Rebuilding resets the full-text metadata in the database system tables.|
If change tracking is OFF, rebuilding does not cause a repopulation of the newly created full-text catalog. In this case, to repopulate, execute sp_fulltext_catalog 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 the use of the default location specified at setup. This is the Ftdata subdirectory in the Mssql directory; for example, C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\FTData. The specified root directory must 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 an instance of SQL Server.
@path is valid only when action is create. For actions other than create (stop, rebuild, and so on), @path must be NULL or omitted.
If the instance of SQL Server is a virtual server in a cluster, the catalog directory specified needs to be on a shared disk drive on which the SQL Server resource depends. If @path is not specified, the location of default catalog directory is on the shared disk drive, in the directory that was specified when the virtual server was installed.
0 (success) or 1 (failure)
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 re-index only the changed rows in the database. Incremental population can be applied only if the table has a column of the type timestamp. If a table in the full-text catalog does not contain a column of the type timestamp, the table undergoes a full population.
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 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.
The caller is required to be member of the db_owner role. Depending on the action requested, the caller should not be denied ALTER or CONTROL permissions (which db_owner has) on the target full-text catalog.
This example creates an empty full-text catalog, Cat_Desc, in the AdventureWorks2012 database.
USE AdventureWorks2012; GO EXEC sp_fulltext_catalog 'Cat_Desc', 'create'; GO
This example rebuilds an existing full-text catalog, Cat_Desc, in the AdventureWorks2012 database.
USE AdventureWorks2012; GO EXEC sp_fulltext_catalog 'Cat_Desc', 'rebuild'; GO
This example begins a full population of the Cat_Desc catalog.
USE AdventureWorks2012; GO EXEC sp_fulltext_catalog 'Cat_Desc', 'start_full'; GO
This example stops the population of the Cat_Desc catalog.
USE AdventureWorks2012; GO EXEC sp_fulltext_catalog 'Cat_Desc', 'stop'; GO
This example removes the Cat_Desc catalog.
USE AdventureWorks2012; GO EXEC sp_fulltext_catalog 'Cat_Desc', 'drop'; GO
System Stored Procedures (Transact-SQL)