Export (0) Print
Expand All

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:
    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:
    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:
    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:
    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:
    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:
    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:
    EXECUTE sp_fulltext_catalog  'AdvDocFTCat',  'start_full';
    GO 
    
    ms142549.note(en-US,SQL.90).gifNote:
    Full-text catalogs can be created, dropped, and modified as needed; however, avoid making schema changes on multiple catalogs at the same time.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft