EXEC sp_fulltext_database 'enable'
go
IF EXISTS (select  ssche.name as TABLE_OWNER,		sobj.name as TABLE_NAME,		sidx.name as FULLTEXT_KEY_INDEX_NAME,
		sidxcol.column_id as FULLTEXT_KEY_COLID,		sftidx.is_enabled as FULLTEXT_INDEX_ACTIVE,		scat.name as FULLTEXT_CATALOG_NAME
	from    sys.objects as sobj 
		join sys.fulltext_indexes as sftidx on (sobj.object_id = sftidx.object_id)
		join sys.indexes as sidx on(sftidx.unique_index_id = sidx.index_id and sftidx.object_id = sidx.object_id)
		join sys.schemas as ssche on (sobj.schema_id = ssche.schema_id)
		join sys.index_columns as sidxcol on (sftidx.object_id = sidxcol.object_id and sftidx.unique_index_id = sidxcol.index_id)
		left outer join sys.fulltext_catalogs as scat on (scat.fulltext_catalog_id = sftidx.fulltext_catalog_id)
where sobj.name ='CONTENTS')
	DROP FULLTEXT INDEX ON CONTENTS
IF EXISTS (select  ssche.name as TABLE_OWNER,		sobj.name as TABLE_NAME,		sidx.name as FULLTEXT_KEY_INDEX_NAME,
		sidxcol.column_id as FULLTEXT_KEY_COLID,		sftidx.is_enabled as FULLTEXT_INDEX_ACTIVE,		scat.name as FULLTEXT_CATALOG_NAME
	from    sys.objects as sobj 
		join sys.fulltext_indexes as sftidx on (sobj.object_id = sftidx.object_id)
		join sys.indexes as sidx on(sftidx.unique_index_id = sidx.index_id and sftidx.object_id = sidx.object_id)
		join sys.schemas as ssche on (sobj.schema_id = ssche.schema_id)
		join sys.index_columns as sidxcol on (sftidx.object_id = sidxcol.object_id and sftidx.unique_index_id = sidxcol.index_id)
		left outer join sys.fulltext_catalogs as scat on (scat.fulltext_catalog_id = sftidx.fulltext_catalog_id)
where sobj.name ='DOCUMENTS')
	DROP FULLTEXT INDEX ON Documents
GO
GO
declare @ftc as sysname
IF EXISTS(select name from sys.fulltext_catalogs)
begin
	select @ftc = 'DROP FULLTEXT CATALOG ' + name from sys.fulltext_catalogs
	exec sp_sqlexec @ftc
end
CREATE FULLTEXT CATALOG FT_Catalog
go
CREATE FULLTEXT INDEX ON Contents
(
Text
Language 1033
)
KEY INDEX PK_Contents ON FT_Catalog
WITH CHANGE_TRACKING AUTO
GO
CREATE FULLTEXT INDEX ON Documents
(
DocAscii
Language 1033
)
KEY INDEX PK_Documents ON FT_Catalog
WITH CHANGE_TRACKING AUTO
GO
select case FullTextCatalogProperty('FT_Catalog', 'PopulateStatus')

          when 0 then 'Idle'

          when 1 then 'Full population in progress'

          when 2 then 'Paused'

          when 3 then 'Throttled'

          when 4 then 'Recovering'

          when 5 then 'Shutdown'

          when 6 then 'Incremental population in progress'

          when 7 then 'Building index'

          when 8 then 'Disk is full. Paused.'

          when 9 then 'Change tracking'

          else 'Error reading FullTextCatalogProperty PopulateStatus'

        end


select FullTextCatalogProperty('FT_Catalog', 'PopulateStatus')
/*
SELECT * FROM contents WHERE CONTAINS(text, '"FOLLOW*"') -- prefix Follow
SELECT * FROM contents WHERE CONTAINS(text, '"FOLLOW"') -- exact Follow
SELECT * FROM contents WHERE CONTAINS(text, 'PORV AND SG') -- boolean
SELECT * FROM contents WHERE CONTAINS(text, 'PORV NEAR SG') -- boolean
SELECT * FROM contents WHERE CONTAINS(text, '"SG AND PORV"') -- boolean
SELECT * FROM contents WHERE CONTAINS(text, '"SG*" AND "PORV*"') -- boolean
SELECT * FROM contents WHERE CONTAINS(text, '"SG" AND "PORV"') -- boolean
SELECT * FROM contents WHERE CONTAINS(text, '"SG*" AND "PORV*"') -- boolean
	AND NOT CONTAINS(text, '"SG" AND "PORV"') -- boolean
SELECT * FROM contents WHERE CONTAINS(text, '"SG PORV"') -- boolean
SELECT * FROM contents WHERE text like '%SG PORV%' -- SQL Search

SELECT * FROM contents WHERE CONTAINS(text, 'FORMSOF(INFLECTIONAL, "foot")'); -- inflectional foot
SELECT * FROM contents WHERE CONTAINS(text, '"run*"'); -- prefix run
SELECT * FROM contents WHERE CONTAINS(text, 'FORMSOF(INFLECTIONAL, "run")'); -- inflectional run
SELECT * FROM contents WHERE CONTAINS(text, '"run*"')
 AND NOT CONTAINS(text, 'FORMSOF(INFLECTIONAL, "run")'); -- prefix run and not inflectional run
SELECT * FROM contents WHERE CONTAINS(text, '"vacuum decrease*"'); -- prefix vacuum decrease

SELECT count(*) DG_or_DieselGenerator FROM contents where text like '%DG%' OR text like '%DIESEL GENERATOR%'
SELECT count(*) DG FROM contents where text like '%DG%'
SELECT count(*) DieselGenerator FROM contents where text like '%DIESEL GENERATOR%'
SELECT *  FROM contents where text like '%DIESEL GENERATOR%'

SELECT * FROM contents WHERE CONTAINS(text, 'FORMSOF(THESAURUS, "DG*")'); -- 
SELECT * FROM contents WHERE CONTAINS(text, 'FORMSOF(THESAURUS, "DIESEL GENERATOR")'); -- 
SELECT * FROM contents WHERE FREETEXT(text, '"DIESEL GENERATOR*"'); -- 
SELECT * FROM contents WHERE FREETEXT(text, '"DG*"'); -- 
SELECT * FROM contents WHERE CONTAINS(text, 'FORMSOF(THESAURUS, "SG")'); -- 
SELECT * FROM contents WHERE CONTAINS(text, 'FORMSOF(THESAURUS, "STEAM GENERATOR")'); -- 

EXEC sp_fulltext_load_thesaurus_file 1033;
SELECT * FROM contents WHERE CONTAINS(text, '"RUN*" and "PUMP*"'); -- 
SELECT * FROM contents WHERE CONTAINS(text, 'FORMSOF(INFLECTIONAL, "run") and "PUMP*"'); -- 
SELECT * FROM contents WHERE CONTAINS(text, '"RUN*" and "PUMP*"') -- 
  AND NOT CONTAINS(text, 'FORMSOF(INFLECTIONAL, "run") and "PUMP*"'); -- 
*/