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