Below is the script I learned while in Seattle attending PASS conference. Some guys from Microsoft helped me to rebuild full text index on some SharePoint databases I manage.

select * from sys.fulltext_catalogs

sp_configure ‘show adv’,0
reconfigure

— Get the fulltext catalog name to rebuild
select object_name(object_id), * from sys.fulltext_indexes

select * from docs where 1=0

select * from docs where contains(*,’SomeString’)

select extension,
count(*) from docs where extension <> ” group by extension

distinct extension is not null contains(*,’SomeString’)

select count(*) from docs

select * from master.dbo.sysfiles

–Rebuild fulltext catalog and index
ALTER FULLTEXT CATALOG ix_MyFullText REBUILD with accent_sensitivity=on

select fulltextcatalogproperty(‘ix_Catalog’, ‘populatestatus’)
select fulltextcatalogproperty(‘ix_Catalog’, ‘itemcount’)