Share this with your friends 

Full text search Implementation

7 ratings Views 715 
Author: TEJINDER (TEJINDER SINGH)  View Profile |  View other solutions by this author

Question / Problem

How to Implement Fulltext search in sql server 2005


--implementing fulltext search on a column.

-- Enabling full text search on current database

Exec sp_fulltext_database enable

--1 Create the catalog (unless you already have)

EXEC sp_fulltext_catalog 'FTCatalog','create'

--2 Add a full text index to a table

EXEC sp_fulltext_table 'HumanResources.Department', 'create', 'FTCatalog', 'PK_Department_DepartmentID'
EXEC sp_fulltext_table 'HumanResources.Employee', 'create', 'FTCatalog', 'PK_Employee_EmployeeID'

--3 Add a column to the full text index

EXEC sp_fulltext_column 'HumanResources.Department', 'Name', 'add'
EXEC sp_fulltext_column 'HumanResources.Employee', 'LoginID', 'add'

--4 Activate the index

EXEC sp_fulltext_table 'HumanResources.Department','activate'
EXEC sp_fulltext_table 'HumanResources.Employee','activate'

--5 Start full population

EXEC sp_fulltext_catalog 'FTCatalog', 'start_full'

/*Example of full text queries*/

select * from HumanResources.Employee
where freetext(LoginID, 'work')

select * from HumanResources.Employee
where (LoginID like 'adventure-works%')

select * from HumanResources.Employee
where contains(LoginId, 'works')

Applies to

Microsoft SQL Server 2005

Rank It

Login to rank it