Performing Investigation and Clean-up Tasks for Full-Text Catalogs
In this example, you perform typical investigation and clean-up tasks. Assume that you are connected to the AdventureWorks database, the full-text service has been started, you are working with the Document table, and that AdvDocFTCat is the full-text catalog associated with the Document table.
Obtain a list of all the full-text catalogs linked to the AdventureWorks database by running this query:
SELECT name FROM sys.fulltext_catalogs; GO
Because the AdventureWorks database is the current database, this returns metadata for all the full-text catalogs linked to the AdventureWorks database.
For details on the metadata returned, see sys.fulltext_catalogs (Transact-SQL).
Obtain a list of all the tables in the database that have been enabled for full-text processing by executing this stored procedure:
This stored procedure returns the following metadata for each table:
- The schema associated with the table
- The name of the table
- The name of the index that is used to impose a unique constraint on the full-text key column
- The integer identifier of the column used as the table's full-text key
- The full-text status of the table
- The name of the full-text catalog of the table
Two other variations of this stored procedure are supported. If the fulltext_catalog_name parameter is specified, this information is returned for all the tables linked to that full-text catalog. If both the catalog_name and table_name parameters are specified, or if just the table_name parameter is specified, then this information is returned for that table.
For more information, see sp_help_fulltext_tables (Transact-SQL)
Obtain a list of all the columns in the database that have been enabled for full-text processing by executing this stored procedure:
This stored procedure returns the following metadata about each column:
- The schema associated with the table
- The name and integer identifier of the table
- The name and integer identifier of the column that is full-text enabled
- The name and integer identifier of the column in a full-text indexed table that specifies the document type of the full-text indexed column
- The language used for the full-text search of the column.
A variation of this stored procedure, in which a table name parameter is specified, returns this information for a single table.
For more information, see sp_help_fulltext_columns (Transact-SQL).
Unregister the Document table for full-text processing by running the following statement:
DROP FULLTEXT INDEX ON Production.Document; GO
For more information, see DROP FULLTEXT INDEX (Transact-SQL).
This drops the metadata about full-text indexing for the Document table. The existing full-text index remains in place until the next full population or until the full-text catalog is dropped. However, it remains unused.
Drop the AdvDocFTCat full-text catalog from the file system and its metadata from the catalog views by running the following statement:
DROP FULLTEXT CATALOG AdvDocFTCat; GO
For more information, see DROP FULLTEXT CATALOG (Transact-SQL).
You must complete Step 4 before a full-text catalog can be dropped because its full-text catalog metadata must be updated to remove all full-text indexes.
There is at least one full-text catalog in the file system that no longer has corresponding Microsoft SQL Server metadata. The usual cause of this is the removal of a database.
Rebuild, but do not repopulate, the AdvDocFTCat full-text catalog by executing this stored procedure:
EXECUTE sp_fulltext_catalog 'AdvDocFTCat', 'Rebuild'; GO
For more information, see sp_fulltext_catalog (Transact-SQL).
The sp_fulltext_database stored procedure with the ENABLE option may be used to rebuild all known full-text catalogs.
Start a full population of the AdvDocFTCat full-text catalog by executing this stored procedure:
EXECUTE sp_fulltext_catalog 'AdvDocFTCat', 'start_full'; GO
Full-text catalogs can be created, dropped, and modified as needed; however, avoid making schema changes on multiple catalogs at the same time.