A complete SQL Server full-text index example

An example of full-text index of the SQL Server database, using the pubs database as an example.
The following describes how to create a full-text index using the system stored procedure:
  1  (Sp_fulltext_database)
  2  ) Create a full-text directory (sp_fulltext_catalog)
  3  (Sp_fulltext_table)
  4  (Sp_fulltext_column)
  5  ) Create a full-text index for the table (sp_fulltext_table)
  6 ) Fill in the full-text directory (sp_fulltext_catalog)
  --  ------- ********* Example ********-------------  
  Create a full-text index for the title and notes columns of the pubs database, and then use the index to query the name of the book containing the Datebase or computer string in the title column or notes column:
Before that, you need to install the Microsoft Search Service and start the full-text search service for SQL Server.
  User  Pubs  --  Open Database  
  Go   
  --  Check whether the database pubs supports full-text indexing. If not 
--  Use sp_fulltext_database to enable this function.  
  If  (  Select     Databaseproperty  (  '  Pubs  '  ,  '  Isfulltextenabled  '  ))  = 0   
  Execute  Sp_fulltext_database  '  Enable  '   
  --  Create the full-text directory ft_pubs  
  Execute  Sp_fulltext_catalog  '  Ft_pubs  '  , '  Create  '   
  --  Create full-text index data elements for the title table  
  Execute  Sp_fulltext_table  '  Title  '  ,  '  Create  '  ,  ' Ft_pubs  '  ,  '  Upkcl_titleidind  '   
  --  Set full-text index column name  
  Execute  Sp_fulltext_column  '  Title  '  ,  '  Title '  ,  '  Add  '   
  Execute  Sp_fulltext_column  '  Title  '  ,  '  Notes  '  ,  '  Add '   
  --  Create full-text index  
--  Activate: Enables full-text retrieval of a table, that is, registering the table in the full-text directory.  
  Execute  Sp_fulltext_table  '  Title  '  ,  '  Activate  '   
 --  Fill the full-text INDEX DIRECTORY  
  Execute  Sp_fulltext_catalog  '  Ft_pubs  '  ,  '  Start_full  '   
  Go   
  --  Check full-text directory Filling  
 While     Fulltextcatalogproperty  (  '  Ft_pubs  '  ,  '  Populatestatus  '  )  <>  0   
  Begin   
 --  If the full-text directory is being filled, wait 30 seconds and check again.  
  Waitfor  Delay  '    '   
  End   
  --  After the full-text directory is filled, you can use full-text directory retrieval  
  Select  Title
Form
  Where    Contains  (Title,  '  Database  '  )
  Or     Contains  (Title,  '  Computer  '  )
  Or     Contains (Notes,  '  Database  '  )
  Or     Contains  (Notes,  '  Database  '  )

  '  -------------- The following describes the full-text operating system stored procedures 
 process name: sp_fulltext_service 
 execution permission: serveradmin or system administrator 
 usage: set full-text search attributes 
 process name: sp_fulltext_catalog 
 execution permission: db_owner and higher role members 
: create and delete a full-text directory, start or stop the index operation of a full-text directory 
 process name: sp_fulltext_database 
 execution permission: db_owner role member 
 for use: initialize full-text index or delete all full-text directories in the database 
 process name: sp_fulltext_table 
 execution permission: db_ddladnmin or db_owner role member 
 for use: identify a table as a full-text index table or a non-full-text index table 
 process name: sp_fulltext_column 
 execution permission: db_ddladnmin role member 
 for use: specifies the columns in a full-text index table. If the full-text index is left or not,