Wednesday, October 10, 2007

Handy-Dandy Full Text Index Stored Procedures

One of the host I use has SQL Server 2000 databases, but the Full Text Index management features through Enterprise Manager are disabled, so I have to do all my Full Text Index stuff through Query Analyzer.

Here are the stored procedures you can use to create, add tables too, and populate the index.

Handy-dany system stored procedures for setting up a full text index on SQL Server 2000:

Enable full text indexing on the database:
EXEC sp_fulltext_database  'enable'
Create a full text catalog
EXEC sp_fulltext_catalog   'FullTextCatalogName','create'
Enable full text index on a table
EXEC sp_fulltext_table 'TableName', 'create', 'FullTextCatalogName', 'PK_TableName'
Add a column to the Full Text Catalog
EXEC sp_fulltext_column    'TableName', 'ColumnName', 'add'
Activate the index
EXEC sp_fulltext_table     'TableName','activate'
Start full population
EXEC sp_fulltext_catalog   'FullTextCatalogName', 'start_full'
Set Change tracking with the Background option
EXEC sp_fulltext_table 'TableName', 'Start_change_tracking'
EXEC sp_fulltext_table 'TableName', 'Start_background_updateindex'
Note: Change tracking does not track any WRITETEXT or UPDATETEXT operations.

No comments: