4

How do I find a list of tables that have a FULLTEXT INDEX applied to them on a given database?

For example, you can enable and disable a FULLTEXT INDEX like this:

ALTER FULLTEXT INDEX ON [dbo].[OBJECT_FACT] ENABLE

ALTER FULLTEXT INDEX ON [dbo].[OBJECT_FACT] DISABLE
10

To find a list of tables that have a FULLTEXT INDEX:

SELECT
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName, 
    c.name AS FTCatalogName ,
    f.name AS FileGroupName,
    i.name AS UniqueIdxName,
    cl.name AS ColumnName
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.filegroups f
ON
    fi.data_space_id = f.data_space_id
INNER JOIN 
    sys.indexes i
ON 
    fi.unique_index_id = i.index_id
    AND fi.[object_id] = i.[object_id];

To enable and disable a FULLTEXT INDEX refer msdn

sp_fulltext_table 
   [ @tabname= ] 'qualified_table_name'         
   , [ @action= ] 'action' 
   [ 
   , [ @ftcat= ] 'fulltext_catalog_name'         
   , [ @keyname= ] 'unique_index_name' 
   ]

The preferred method is to use ALTER FULLTEXT INDEX [myFTindex] {ENABLE/DISABLE} instead of the deprecated sp_fulltext_table.

2

Paul's answer is great and helped me a lot but I was wondering why some of mine weren't showing up, the reason is that some of my FT indexes were on indexed views rather than tables.

A slight amendment to incorporate indexed views as well as tables would be:

SELECT SCHEMA_NAME(o.schema_id) AS SchemaName,
       o.name AS ObjectName,
       c.name AS FTCatalogName,
       f.name AS FileGroupName,
       i.name AS UniqueIdxName,
       cl.name AS ColumnName
FROM sys.objects o
    INNER JOIN sys.fulltext_indexes fi
        ON o.[object_id] = fi.[object_id]
    INNER JOIN sys.fulltext_index_columns ic
        ON ic.[object_id] = o.[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.filegroups f
        ON fi.data_space_id = f.data_space_id
    INNER JOIN sys.indexes i
        ON fi.unique_index_id = i.index_id
           AND fi.[object_id] = i.[object_id];

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.