Funded in: http://bbs.hidotNet.com/showpost.aspx? Postid = 67
An example of a full-text index of the SQL Server database. (As an example of PUBS database)
First, introduce the specific steps to create a full-text index using the system stored procedure:
1) The full-text processing function of the launch database (sp_fulltext_database) 2) Establish a full-text directory (sp_fulltext_catalog) 3) Sign up in full-text directory (sp_fulltext_table) 4) indicates the list of full-text indexes (sp_fulltext_column) 5) Create a full-text index (sp_fulltext_table) 6) Full-text directory (sp_fulltext_catalog)
--------- ******** Example ******** ------------ Establish a full text with the title and notes columns of the Pubs database Index, then use the index query Title column or notes column containing a DateBase or Computer string:
Before this, you need to install Microsoft Search service, start SQL Server full-text search service
User Pubs - Open Database Go - Check if the database PUBS supports full-text index, if not support - use sp_fulltext_database to open this function if (select DatabaseProperty ('pubs', 'isfultextEnabled') = 0 execute sp_fultext_database 'enable'
- Establish a full-text directory ft_pubsexecute sp_fulltext_catalog 'ft_pubs', 'create'
- Establish full-text index data EXECUTE SP_FULLTEXT_TABLE 'TITLE', 'CREATE', 'FT_PUBS', 'UPKCL_TITLEIDIND'
- Set the full-text index column name EXECUTE sp_fultext_column 'title', 'title', 'add'execute sp_fultext_column' title ',' notes', 'add'
- Establish a full-text index -activate, which is the full text search capability of the activation table, that is, registered in the full-text directory EXECUTE SP_FULLTEXT_TABLE 'TITLE', 'ACTIVATE'
- Filled full-text index directory execute sp_fulltext_catalog 'ft_pubs',' Start_Full'go
- Check the full-text catalog filler While FullTextCatalogProperty ('ft_pubs', 'Po *** Testatus') <> 0begin
- If the full-text catalog is in the filler, wait for 30 seconds, then detect a Waitfor Delay '0: 0: 30'end
- After the full-text catalog is completed, you can use the full-text directory search.
Select TitleFormwhere Contains (Title, 'Database') OR Contains (Notes, 'Database') or Contains (Notes, 'Database')
'-------------- The following describes the system stored procedure of the full text operation class: SP_FULLTEXT_SERVICE Execution Permissions: ServerAdmin or System Administrator Ren: Set the full-text search attribute process name: sp_fulltext_catalog Execution Permissions: DB_OWNER and Higher Role Members: Create and delete a full-text directory, start or stop an index operation of a full-text directory
Procedure Name: sp_fulltext_database Execution Permissions: DB_OWNER Role Members: Initializing Full Text Index or Delete All Full Directory in Database
Procedure Name: sp_fulltext_table Execution Permissions: DB_DDLADNMIN or DB_OWNER Role Member: Tag identifies a table 1 as a full-text index table or non-full-text index
Process Name: sp_fulltext_column Execution Permissions: DB_DDLADNMIN Role Members: Point out the columns in a full-text index table, if you quit full-text index
