
-- load new IFilters
EXEC sp_fulltext_service 'load_os_resources', 1

-- check that it contains the suffixes you're using
SELECT document_type, path FROM sys.fulltext_document_types

CREATE DATABASE test
GO

USE test
GO

CREATE TABLE dbo.docs (
 id INT IDENTITY NOT NULL,
 doctype VARCHAR(20),
 doc VARBINARY(MAX), 
   CONSTRAINT pk_id PRIMARY KEY (id)
)
GO 

-- from BOL
CREATE SEARCH PROPERTY LIST DocumentProperties;
GO
ALTER SEARCH PROPERTY LIST DocumentProperties
   ADD 'Title' 
   WITH ( PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', PROPERTY_INT_ID = 2, 
      PROPERTY_DESCRIPTION = 'System.Title - Title of the item.' );
ALTER SEARCH PROPERTY LIST DocumentProperties
    ADD 'Author'
   WITH ( PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', PROPERTY_INT_ID = 4, 
      PROPERTY_DESCRIPTION = 'System.Author - Author or authors of the item.' );
ALTER SEARCH PROPERTY LIST DocumentProperties 
    ADD 'Tags'
   WITH ( PROPERTY_SET_GUID = 'F29F85E0-4FF9-1068-AB91-08002B27B3D9', PROPERTY_INT_ID = 5, 
      PROPERTY_DESCRIPTION = 'System.Keywords - Keywords (Tags) of the item.' );
GO

CREATE FULLTEXT CATALOG FTCatalog AS DEFAULT;
GO

CREATE FULLTEXT INDEX ON docs(
Doc TYPE COLUMN doctype LANGUAGE English
)
KEY INDEX pk_id ON (FTCatalog)
WITH (SEARCH PROPERTY LIST = DocumentProperties)
go

ALTER FULLTEXT INDEX ON docs
   START FULL POPULATION;
GO

-- until status = 0 (populated, idle)
SELECT OBJECTPROPERTYEX(OBJECT_ID('docs'),'TableFulltextPopulateStatus')
go

-- Office 2010 document, Author = Bob Beauchemin, Title = Sample Document
DECLARE @x VARBINARY(MAX)
SET @x = ( 
 SELECT * FROM OPENROWSET
  (BULK 'c:\temp\SampleDoc.docx', SINGLE_BLOB) AS X)

INSERT INTO dbo.docs VALUES('.docx',@x)
go

-- search property list and related metadata
SELECT * FROM sys.registered_search_properties 
SELECT * FROM sys.registered_search_property_lists
SELECT * FROM sys.dm_fts_index_keywords_by_property(
 DB_ID('test'), 
 OBJECT_ID('docs'))

-- search by property
SELECT * FROM dbo.docs WHERE CONTAINS(PROPERTY(doc,'Title'), 'Sample');
GO

SELECT * FROM dbo.docs WHERE CONTAINS(PROPERTY(doc,'Author'), 'Bob');
GO

-- this (search content + properties) works too
SELECT * FROM dbo.docs WHERE CONTAINS(doc, 'Bob');
GO

