Get Fulltext Index Composition

USE MyDatabase

SELECT

tblOrVw.[name] AS TableOrViewName,

tblOrVw.[type_desc] AS TypeDesc,

tblOrVw.[stoplist_id] AS StopListID,

c.name AS FTCatalogName ,

cl.name AS ColumnName,

i.name AS UniqueIdxName

FROM

(

SELECT TOP (1000)

idxs.[object_id],

idxs.[stoplist_id],

tbls.[name],

tbls.[type_desc]

FROM sys.fulltext_indexes idxs

INNER JOIN sys.tables tbls

on tbls.[object_id] = idxs.[object_id]

union all

SELECT TOP (1000)

idxs.[object_id],

idxs.[stoplist_id],

tbls.[name],

tbls.[type_desc]

FROM sys.fulltext_indexes idxs

INNER JOIN sys.views tbls -- 'tbls' reused here to mean 'views'

on tbls.[object_id] = idxs.[object_id]

) tblOrVw

INNER JOIN sys.fulltext_indexes fi

on tblOrVw.[object_id] = fi.[object_id]

INNER JOIN

sys.fulltext_index_columns ic

ON

ic.[object_id] = tblOrVw.[object_id]

INNER JOIN

sys.columns cl

ON

ic.column_id = cl.column_id

AND ic.[object_id] = cl.[object_id]

INNER JOIN

sys.fulltext_catalogs c

ON

fi.fulltext_catalog_id = c.fulltext_catalog_id

INNER JOIN

sys.indexes i

ON

fi.unique_index_id = i.index_id

AND fi.[object_id] = i.[object_id];