Finding SQL Server’s Full-Text indexes

I have a lot of servers.  Full-Text operations sort of slipped up on me.  Then, a developer asked for assistance…in particular who else was using full-text that he could ask for guidance?  I couldn’t tell him.  That got me started to find a way to see just how wide-spread full-text had become.  So, how DO you determine which servers and which databases are using full-text?   Well, first Full-text has to be installed.  For SQL 2005, you need  MSFTESQL service running. For 2008, you need fdhost.exe.  In SQL 2008 full-text is by default enabled, but as you see in the code below, I check to see that it is on.  This can be true for all your databases.  But, for Full-text to actually be used…you have to have created an index.  It is for these indexes that I search.  (I assume that the presence of an index indicates Full-text is being used.)

If you have a single SQL server use the following code:

exec master.dbo.sp_msforeachdb
' use ?
declare @iff int
SELECT @iff = fulltextserviceproperty(''IsFullTextInstalled'') + databaseproperty( db_name (), ''IsFulltextEnabled'') + (select count(*) from sys.fulltext_catalogs)
if @iff > 1
iF (select count(*) from sys.fulltext_catalogs) > 0
select db_name() as DBname, name as CatalogName, path, (select count(*) from sys.fulltext_indexes) as NumTablesIndexed from sys.fulltext_catalogs
end ; '

If you have multiple servers use the same code, but apply it to multiple servers. How do you do that?  In SSMS, view Registered Servers, right-click a group name of one of your groups…select “New Query”.  Presto!  You’ll have a single window that list the total number of servers that are in the group you are about to query.  Done!

Best source:

Also a great source:


Leave a Reply

Your email address will not be published. Required fields are marked *