sp_fulltext_database (Transact-SQL)

Updated: 17 July 2006

Initializes full-text indexing or removes all full-text catalogs from the current database. By default, all user created databases in Microsoft SQL Server 2005 are enabled for full-text indexing, unless they are created using SQL Server Management Studio. To enable a database for full-text search when creating a database by using Management Studio, see How to: Create a Database (SQL Server Management Studio).

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 Management Studio instead. For more information, see How to: Enable a Database for Full-Text Indexing (SQL Server Management Studio).

Topic link icon Transact-SQL Syntax Conventions

sp_fulltext_database [@action=] 'action'

[ @action=] 'action'

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

Value Description


Enables full-text indexing within the current database.

This action does not cause index population to begin; an explicit start_full or start_incremental on each catalog must be issued using sp_fulltext_catalog to populate or repopulate the full-text index.


Removes all full-text catalogs in the file system for the current database and marks the database as being disabled for full-text indexing. This action does not change any full-text index metadata at the full-text catalog or table level.

0 (success) or 1 (failure)

Disabling full-text indexing does not remove rows from sysfulltextcatalogs and does not indicate that full-text enabled tables are no longer marked for full-text indexing. All the full-text metadata definitions are still in the system tables. It does indicate that full-text indexing is turned off for the database and no full-text indexing activity can occur.

Only members of the sysadmin fixed server role and db_owner fixed database role can execute sp_fulltext_database.

A. Enabling a database for full-text indexing

The following example enables full-text indexing for the AdventureWorks database.

USE AdventureWorks;
EXEC sp_fulltext_database 'enable';

B. Removing all catalogs from a database

The following example disables full-text indexing for the AdventureWorks database.

USE AdventureWorks;
EXEC sp_fulltext_database 'disable';

Release History

17 July 2006

New content:
  • Added text that states user-created databases are not enabled for full-text indexing by default when they are created by using Management Studio.

Community Additions