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.

1. Obtain a list of all the full-text catalogs linked to the AdventureWorks database by running this query:
   
   Copy

   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).
   
2. Obtain a list of all the tables in the database that have been enabled for full-text processing by executing this stored procedure:
   
   Copy

   sp_help_fulltext_tables;
   GO
   

   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)
   
3. Obtain a list of all the columns in the database that have been enabled for full-text processing by executing this stored procedure:
   
   Copy

   sp_help_fulltext_columns;
   GO
   

   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).
   
4. Unregister the Document table for full-text processing by running the following statement:
   
   Copy

   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.
   
5. Drop the AdvDocFTCat full-text catalog from the file system and its metadata from the catalog views by running the following statement:
   
   Copy

   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.
   
6. Rebuild, but do not repopulate, the AdvDocFTCat full-text catalog by executing this stored procedure:
   
   Copy

   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.
   
7. Start a full population of the AdvDocFTCat full-text catalog by executing this stored procedure:
   
   Copy

   EXECUTE sp_fulltext_catalog  'AdvDocFTCat',  'start_full';
   GO 
   

   Note:
   Full-text catalogs can be created, dropped, and modified as needed; however, avoid making schema changes on multiple catalogs at the same time.