An example of a complete Full-text index for a SQL Server database

The following is an example of a full-text index for SQL Server databases, as in the pubs database. Need a friend under the reference

first, introduce the steps to create a FULL-TEXT index using system stored procedures:


1 ) to start the Full-text processing function of the database (sp_fulltext_database)


2 ) to establish a Full-text catalog (sp_fulltext_catalog)


3 registers the table (sp_fulltext_table) that requires Full-text indexing in the Full-text catalog


4 ) indicates the column name (sp_fulltext_column) in the table that requires a Full-text index


5 ) to create a Full-text index (sp_fulltext_table) for a table


6 ) populate the Full-text catalog (sp_fulltext_catalog)





---------******** example ********-------------
to establish a Full-text index of the title and notes columns of the pubs database, and then use the index query Title column or a notes column that contains a datebase or computer string in the book name:


before this, you need to install the Microsoft Search service to start the SQL Server Full-text Search service


User Pubs--Open database
Go


--Check to see if the database pubs support Full-text indexing if you do not support
--use sp_fulltext_database to turn on the feature
if (select Databaseproperty (' pubs ', ' isfulltextenabled ')) =0


execute sp_fulltext_database ' Enable '


--Establishing Full-text catalogs Ft_pubs
Execute sp_fulltext_catalog ' ft_pubs ', ' Create '


--to establish a full-text indexed data element for the title table
Execute sp_fulltext_table ' title ', ' Create ', ' ft_pubs ', ' Upkcl_titleidind '


--Set full-text indexed column names
Execute sp_fulltext_column ' title ', ' title ', ' Add '


execute sp_fulltext_column ' title ', ' Notes ', ' Add '


--Establishing Full-text indexing
--activate is the ability to activate the Full-text retrieval of a table, that is, to register the table in a Full-text catalog
Execute sp_fulltext_table ' title ', ' Activate '


--populating Full-text index catalogs
Execute sp_fulltext_catalog ' ft_pubs ', ' start_full '


Go


--checking Full-text catalog fills
While FullTextCatalogProperty (' ft_pubs ', ' populatestatus ') <>0


begin


--If the Full-text catalog is in a filled state, wait 30 seconds before testing again
WAITFOR DELAY ' 0:0:30 '


End


-When Full-text catalog population is complete, you can use the Full-text catalog to retrieve
Select Title


Form


where CONTAINS (title, ' database ')


or CONTAINS (title, ' computer ')


or CONTAINS (notes, ' database ')


or CONTAINS (notes, ' database ')





' --------------The following describes the system stored procedures for the Full-text operating class
procedure name: sp_fulltext_service
Execute permissions: serveradmin or system administrator
role: Set full-Text Search properties
procedure name: sp_fulltext_catalog
Execute permissions: DB_Owner and higher role members
role: Create and delete a Full-text catalog, start or stop index operations for a Full-text catalog
procedure name: sp_fulltext_database
Execute permission: db_owner role member
effect: Initialize full-text indexing or delete all Full-text catalogs in a database
procedure name: Sp_fulltext_ Table
Execute permissions: db_ddladnmin or db_owner role members
effect: Identify a table as a full-text indexed table or a non-full-text indexed table
Procedure name: sp_fulltext_column
Execute permission: db_ddladnmin role member
role: Indicate those columns in a full-text indexed table if or exit Full-text indexing