Troubleshooting: 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 AdventureWorks2008R2 database, you are working with the Document table, and that AdvDocFTCat is the full-text catalog associated with the Document table.
For a SQL Server 2008 database, a full-text catalog is a logical concept that refers to a group of full-text indexes. The full-text catalog is a virtual object that does not belong to any filegroup.
The investigation and clean-up tasks are as follows:
Obtain a list of all the full-text catalogs linked to the AdventureWorks2008R2 database by running this query on the sys.fulltext_catalogs (Transact-SQL) catalog view:
SELECT name FROM sys.fulltext_catalogs; GO
Because the AdventureWorks2008R2 database is the current database, this returns metadata for all the full-text catalogs linked to the AdventureWorks2008R2 database.
To get list of indexes in the database use the sys.fulltext_indexes catalog view.
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.