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
begin
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: http://msdn.microsoft.com/en-us/library/ms142541.aspx
Also a great source: http://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/