Summary of how to manage FULL TEXT indexing in TSQ

--Implementing Full Text Search with T-SQL stored Procedures / Eli Leiba

Enabeling Full Text search in T-SQL is usually not so "popular" as doing it with the EnterPrize Manager

So Here are the T-SQL steps you have to do in order to implement FTS in T-SQL

--1 Enabling full text on the database
EXEC sp_fulltext_database  'enable' 

--2 Create the Catalog (if 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' 

-- usage in T-SQL (CONTAINS and FREETEXT predicates)
-- Using the index in T-SQL 
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