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

server| Example | data | database | Index First, describe the steps to create a FULL-TEXT index using system stored procedures:

1 full-text processing of the start database (sp_fulltext_database)
2) Establishment of Full-text catalogs (sp_fulltext_catalog)
3 to register a table in the Full-text catalog that requires Full-text indexing (sp_fulltext_table)
4 indicates the column names in the table that require Full-text indexing (sp_fulltext_column)
5 Create a Full-text index for a table (sp_fulltext_table)
6) Populating Full-text catalogs (sp_fulltext_catalog)


---------******** Sample ********-------------
To establish a Full-text index on the title and notes columns of the pubs database, and then use the index query title column or the book name that contains the Datebase or computer string in the Notes column:

Before that, 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 '

--Create a Full-text catalog Ft_pubs
Execute sp_fulltext_catalog ' ft_pubs ', ' create '

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

--Set full-text index column names
Execute sp_fulltext_column ' title ', ' title ', ' Add '
Execute sp_fulltext_column ' title ', ' Notes ', ' add '

--Establish 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

--Check Full-text catalog fill
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 fills are 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 of the Full-text operation 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
Function: Create and delete a Full-text catalog, start or stop indexing operations for a Full-text catalog


Procedure name: sp_fulltext_database
Execute permissions: db_owner role members
Role: 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
Role: Identify a table as a full-text indexed table or a non-full-text indexed table


Procedure name: Sp_fulltext_column
Execute permissions: Db_ddladnmin role members
Role: Indicate those columns in a full-text indexed table if or not to exit the Full-text index


///*******
Author: Jimmy
oicq:11689643
Email:jimmy111@163.net
*********///