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.