Script / Code For Catalog

Posted by Neer under Sql Server on 2/10/2012 | Points: 10 | Views : 1044 | Status : [Member] | Replies : 3
Hi I want to create full text search.

I want to create full text index and catlog,

can any body tell me??

I know its syntax but i dont know what to add,
-----------------------------------------------------------------
CREATE FULLTEXT CATALOG catalog_name
[ON FILEGROUP filegroup ]
[IN PATH 'rootpath']
[WITH <catalog_option>]
[AS DEFAULT]
[AUTHORIZATION owner_name ]

<catalog_option>::=
ACCENT_SENSITIVITY = {ON|OFF}
-----------------------------------------------------------------

what will come in options file group ? , Path ? , Catalog Option ? , Authorization Owner Name etc ? ? ?

-Neer

-Neer


Responses

Posted by: Neer on: 2/11/2012 [Member] Starter | Points: 25

Up
0
Down
No Body Knows This ? ?

-Neer

Neer, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 2/11/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi Nirmal

Are you trying to implement Full-Text index ?

Please proceed with the steps follows...!

/*1. Creating Full-Text Catalog for a database "DatabaseName" */
USE [DatabaseName]

GO
CREATE FULLTEXT CATALOG [Catalog1] WITH ACCENT_SENSITIVITY = ON AS DEFAULT
GO
/*2. Sample Table*/
Create Table Table_Sample1

(
RowID Int Identity(1,1) Primary Key,
SampleData Varchar(100)
)
Go
Insert Table_Sample1(SampleData) Values('Pandian')
Insert Table_Sample1(SampleData) Values('SQL')
Insert Table_Sample1(SampleData) Values('Server')
Insert Table_Sample1(SampleData) Values('SQL Server')
Insert Table_Sample1(SampleData) Values('SQL Server Development')
Insert Table_Sample1(SampleData) Values('SQL Server Administration')
Insert Table_Sample1(SampleData) Values('SQL Pandian')
Insert Table_Sample1(SampleData) Values('Pandian SQL DBA')
Go
/*2. Configuring Full-Text Index and Test*/
Select * from Table_Sample1

Where Contains(SampleData,'SQL')

Error:
------
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Table_Sample1' because it is not full-text indexed.

Reason : we have configured only Full-Text catalog, But not Full-Text Index yet....

Important: The table should have Primary Key.

Implementing Full-Text Index on a Column in a Table:
1. Write Click on Table in which you want to configre Full-Text Index.
2. Click "Full-Text Index" --> Define Full-Text Index...
3. Primary Key index will be selected by default...
4. Select Next --> Select the columns in the table (In which columns you want to perform the free text search)
5. we have one(String) column "SampleData" and select the column
6. Select the language "English" in next column in the same dialog box...
7. Select Next --> Select the Option "Automatically" (Default)
8. Select Next --> Catalog will be selected automatically...
9. Select Next --> Select Next --> Select Finish...
10. The Process status completes with the status "Success" and select Close.

/*3. Test the Free text search*/
Select * from Table_Sample1 Where Contains(SampleData,'SQL')


Rows will be fetched those rows having the word "SQL" in "SampleData" column

Result
RowID	SampleData

------ -----------
2 SQL
4 SQL Server
5 SQL Server Development
6 SQL Server Administration
7 SQL Pandian
8 Pandian SQL DBA


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Neer, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Neer on: 2/13/2012 [Member] Starter | Points: 25

Up
0
Down
still so confuse mr.Pandian S

I m not getting clear idea.. I am trying to do it thru sql query analyser.

-Neer

Neer, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response