在 SQL Server 2008 中查找全文索引

如何在給定數據庫上找到應用了 FULLTEXT INDEX 的表的列表？

例如，您可以像這樣啟用和禁用 FULLTEXT INDEX：

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

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

要查找具有 的表的列表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];

啟用和禁用FULLTEXT INDEX引用msdn

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

首選方法是使用ALTER FULLTEXT INDEX [myFTindex] {ENABLE/DISABLE}而不是棄用的sp_fulltext_table.

引用自：https://dba.stackexchange.com/questions/31646