SQL Server2000全文检索应用实例

以下是SQL Server2000全文检索的具体应用:

exec sp_fulltext_database 'enable'

 

select databaseproperty('pubs','IsFulltextEnables')

 

if (select databaseproperty('pubs','IsFulltextEnables')) is null
 exec sp_fulltext_database 'enable'

 

--建立全文目录FT_pubs
exec sp_fulltext_catalog 'FT_Pubs','create'

--为titles表建立全文索引数据元
exec sp_fulltext_table 'titles','create','FT_Pubs','UPKCL_titleidind'

--设置全文索引列名
exec sp_fulltext_column 'titles','title','add'
exec sp_fulltext_column 'titles','notes','add'

--建立全文索引
exec sp_fulltext_table 'titles','activate','FT_Pubs','UPKCL_titleidind'

--填充全文索引目录
exec sp_fulltext_catalog 'FT_Pubs','start_full'

go

 

--检查全文目录填充情况
while FulltextCatalogProperty('FT_Pubs','PopulateStatus')<>0
begin
 waitfor delay '00:00:30'
end

 

--查询title列或notes列中包含有database或computer字符串的图书名称
select * from titles where contains(title,'database')
or contains(notes,'database') or contains(title,'computer')
or contains(notes,'computer')

exec sp_fulltext_table 'authors','create','FT_Pubs','UPKCL_auidind'

exec sp_fulltext_column 'authors','au_lname','add'
exec sp_fulltext_column 'authors','au_fname','add'

exec sp_fulltext_table 'authors','activate','FT_Pubs','UPKCL_auidind'

exec sp_fulltext_catalog 'FT_Pubs','start_full'

while FulltextCatalogProperty('FT_Pubs','PopulateStatus')<>0
begin
 waitfor delay '00:00:30'
end

 

select * from authors where freetext(au_lname,'White Blue Green Smith')

select databasepropertyex('pubs','IsAutoClose')

select databasepropertyex('pubs','IsAutoCreateStatistics')

select databasepropertyex('pubs','IsAutoShrink')

select databasepropertyex('pubs','IsCloseCursorsOnCommitEnabled')

select databasepropertyex('pubs','IsLocalCursorsDefault')

select databasepropertyex('pubs','Recovery')

select databasepropertyex('pubs','IsAnsiNullDefault')

exec sp_configure

exec sp_dboption

select fulltextserviceproperty('IsFulltextInstalled')

set showplan_all on

go