Thursday, January 5, 2017

How do I know if Full-Text Search is installed?

One of my customers asked me this question yesterday. Is SQL Server Full-Text search installed? Any way to know if it's being used?  This is what I told him.

If this first SELECT statement returns 1 on any of your SQL Server instances, that tells us that Full-Text Search was included in the installation.


That only means it was installed.  It does not mean that it is in use.  This next query will look for fulltext catalogs in any of your user databases.  If it finds a catalog, then we could say that it is being used, or at least that it was used at some point in time.  If nothing is returned, that means it was never put to use.

-- create temp table
CREATE TABLE #fulltextinfo (
  DatabaseName VARCHAR(128),
  [FulltextCatalogName] VARCHAR(128));

-- check each db
INSERT #fulltextinfo (DatabaseName,FulltextCatalogName)
EXEC sp_MSforeachdb 'USE ? SELECT ''?'', name FROM sys.fulltext_catalogs;'

-- see if we found any
SELECT * FROM #fulltextinfo

-- cleanup temp table
DROP TABLE #fulltextinfo;

That's pretty much it, though I'd encourage you take a look at this reference for much more detail on sys.fulltext_catalogs:

No comments:

Post a Comment