Find fulltext indexes in SQL Server 2008
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
.
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];