Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
While it's deprecated and will cease to exist in some future version of SQL Server, through SQL Server 2012 you can still use the system stored procedure sp_help_fulltext_tables. –  Aaron Bertrand Jan 10 '13 at 1:48

1 Answer 1

up vote 6 down vote accepted

To find a list of tables that have a FULLTEXT INDEX

SELECT 
    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' 
   ]
share|improve this answer
    
Oh, I would have figured that out with a guess and check. Haha! NOT! –  MacGyver Jan 9 '13 at 20:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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