List which columns have a full-text index in SQL S

问题:

How do I list all tables / columns in my database that have a full-text index?

回答1:

select distinct
    object_name(fic.[object_id]) table_name,
    [name] column_name
from
    sys.fulltext_index_columns fic
    inner join sys.columns c
        on c.[object_id] = fic.[object_id]
        and c.[column_id] = fic.[column_id]

回答2:

This one gives you more information

SELECT 
    t.name AS TableName, 
    c.name AS FTCatalogName ,
    i.name AS UniqueIdxName,
    cl.name AS ColumnName,
    cdt.name AS DataTypeColumnName
FROM 
    sys.tables t 
INNER JOIN 
    sys.fulltext_indexes fi 
ON 
    t.[object_id] = fi.[object_id] 
INNER JOIN 
    sys.fulltext_index_columns ic
ON 
    ic.[object_id] = t.[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]
LEFT JOIN 
    sys.columns cdt
ON 
    ic.type_column_id = cdt.column_id
    AND fi.object_id = cdt.object_id;