USE AdventureWorks2008r2
GO
IF OBJECT_ID (N'ProductDocs', N'U') IS NOT NULL
DROP TABLE ProductDocs
GO
CREATE TABLE ProductDocs (
  DocID INT NOT NULL IDENTITY,
  DocTitle NVARCHAR(50) NOT NULL,
  DocFilename NVARCHAR(400) NOT NULL,
  FileExtension NVARCHAR(8) NOT NULL,
  DocSummary NVARCHAR(MAX) NULL,
  DocContent VARBINARY(MAX) NULL,
  CONSTRAINT [PK_ProductDocs_DocID] PRIMARY KEY CLUSTERED (DocID ASC)
)
GO
INSERT INTO ProductDocs
(DocTitle, DocFilename, FileExtension, DocSummary, DocContent)
SELECT Title, FileName, FileExtension, DocumentSummary, Document
FROM Production.Document
 
GO

--Create full text catalog
CREATE FULLTEXT CATALOG ProductFTS 
WITH ACCENT_SENSITIVITY = OFF

--Check Collation
SELECT name, collation_name FROM sys.databases 
WHERE name = 'AdventureWorks2008R2'

SELECT fulltext_catalog_id, name FROM sys.fulltext_catalogs

--Create fulltext index
CREATE FULLTEXT INDEX ON ProductDocs
(DocSummary, DocContent TYPE COLUMN FileExtension LANGUAGE 1033)
KEY INDEX PK_ProductDocs_DocID
ON ProductFTS
WITH STOPLIST = SYSTEM

SELECT t.name AS TableName, c.name AS FTCatalogName
FROM sys.tables t JOIN sys.fulltext_indexes i
  ON t.object_id = i.object_id
JOIN sys.fulltext_catalogs c
ON i.fulltext_catalog_id = c.fulltext_catalog_id

SELECT display_term, column_id, document_count 
FROM sys.dm_fts_index_keywords
(DB_ID('AdventureWorks2008R2'), OBJECT_ID('ProductDocs'))