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

An example of a full-text index for SQL Server databases, in the pubs database.


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


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 a table (sp_fulltext_table) that requires Full-text indexing in a Full-text catalog


4 indicates the column names (sp_fulltext_column) that require full-text indexing in the table


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


6) Populate Full-text catalogs (sp_fulltext_catalog)


---------******** Sample ********-------------


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
is not supported

-Opens the feature using Sp_fulltext_database


if (select Databaseproperty (' pubs ', ' isfulltextenabled ')) =0


execute sp_fulltext_database ' Enable '


--Establish Full-text catalog Ft_pubs


execute sp_fulltext_catalog ' ft_pubs ', ' Create '


--Establish Full-text indexing data 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, which is to register the table in a Full-text catalog


execute sp_fulltext_table ' title ', ' Activate '


--Populate the Full-text indexing directory


execute sp_fulltext_catalog ' ft_pubs ', ' start_full '


Go


--Check full-text catalog padding


while FullTextCatalogProperty (' ft_pubs ', ' populatestatus ') <>0


begin


--If the Full-text catalog is in a filled state, wait 30 seconds before checking again


waitfor DELAY ' 0:0:30 '


End


-When Full-text catalog population is complete, you can use Full-text catalogs 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 Full-text operations


process Name: Sp_fulltext_service


Execute permissions: serveradmin or system administrator


function: Set Full-text search Properties


process 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


process Name: Sp_fulltext_database


Execute permission: db_owner role member


function: Initialize full-text indexing or delete all Full-text catalogs in a database


process Name: sp_fulltext_table


Execute permission: db_ddladnmin or db_owner role member


role: Identifies a table as a full-text indexed table or a non-full-text indexed table


process Name: Sp_fulltext_column


Execute permission: db_ddladnmin role member


function: Indicate those columns in a full-text indexed table if or exit Full-text indexing