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.
- Enable full text on the database by executing the following SP:
EXEC sp_fulltext_database 'enable'
- Create the catalog (if it does not exist):
EXEC sp_fulltext_catalog 'MyCatalog','create'
- 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'
- Add a column to the full text index:
EXEC sp_fulltext_column 'Products', 'ProductName', 'add' EXEC sp_fulltext_column 'Categories', 'Description', 'add'
- Activate the index:
EXEC sp_fulltext_table 'Products','activate' EXEC sp_fulltext_table 'Categories','activate'
- Start full population:
EXEC sp_fulltext_catalog 'MyCatalog', 'start_full'
Here are some examples on how to use this procedure:
USE Northwind GO SELECT ProductId, ProductName, UnitPrice FROM Products WHERE CONTAINS( ProductName, ' "sasquatch " OR "stout" ' ) GO
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.
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free SQL Server tips and scripts.
- Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
- Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.
This was first published in February 2005