Stored procedures: Implement full text search

Here are the steps to take to implement full text search using T-SQL stored procedures.

Enabling full text search in T-SQL is not as popular as doing it with the Enterprise Manager. Nonetheless, it can

be useful in certain situations. Here are the steps to implement FTS in T-SQL.

  1. Enable full text on the database by executing the following SP:
    EXEC sp_fulltext_database  'enable'
    
  2. Create the catalog (if it does not exist):
    EXEC sp_fulltext_catalog   'MyCatalog','create' 
    
  3. Add a full text index on a table:
    EXEC sp_fulltext_table     'Products', 'create', 'MyCatalog', 'pk_products'
    EXEC sp_fulltext_table     'Categories', 'create', 'MyCatalog', 'pk_categories'
    
  4. Add a column to the full text index:
    EXEC sp_fulltext_column    'Products', 'ProductName', 'add' 
    EXEC sp_fulltext_column    'Categories', 'Description', 'add'
    
  5. Activate the index:
    EXEC sp_fulltext_table     'Products','activate'
    EXEC sp_fulltext_table     'Categories','activate'
    
  6. Start full population:
    EXEC sp_fulltext_catalog   'MyCatalog', 'start_full'
    

Here are some examples on how to use this procedure:

  1. USE Northwind
    GO
    SELECT ProductId, ProductName, UnitPrice
    FROM Products
    WHERE CONTAINS(  
                        ProductName, ' "sasquatch " OR "stout" '
                                    )
    GO
    
  2. USE Northwind
    GO
    SELECT CategoryName
    FROM Categories
    FREETEXT (
                        Description, 'sweetest candy bread and dry meat'
                       )
    GO
    

About the author

Eli Leiba works at the Israel Electric Company as a senior application DBA in Oracle and Microsoft SQL Server systems. He has certifications from Microsoft and BrainBench in Oracle and SQL Server database administration and implementation. Mr. Leiba holds a B.S. in computer science and has 11 years of experience working in the database field. Additionally, Mr. Leiba teaches SQL Server DBA and development courses at the Microsoft CTEC and serves as a senior database consultant for several Israeli start-up companies.
 

This was first published in February 2005

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close