Wednesday, April 28, 2010

Check if full-text search is enabled

Recently I needed to check whether full-text search functionality is installed and enabled on a Microsoft SQL Server 2008 database.

An easy way to do this is by using the FullTextServiceProperty property. FullTextServiceProperty is a T-SQL function which return information about full-text service installed on the related Microsoft SQL Server instance. You can use FullTextServiceProperty t-sql function especially to check whether fulltext search component is installed on the current SQL Server instance and get the status of the full-text service.

FullTextServiceProperty function uses the property name as an input parameter.

There are 2 properties available that can be used with fulltextserviceproperty function:

  • IsFullTextInstalled : If you pass this property as an input to the FullTextServiceProperty the returned value will be an indicator whether full-text component is installed on the related instance of Microsoft SQL Server instance. Possible return values are 1, 0 and NULL values.
  • If FullTextServiceProperty function combined with IsFullTextInstalled parameter returns 1, this indicated that Full-text is installed. If return value is 0 then full-text service is not installed on the SQL Server.

A sample:

   1:  SELECT FullTextServiceProperty('IsFullTextInstalled')


Tony said...

This doesn't check if it is enabled, it checks if it is installed.

Arthur Zubarev said...

To check if it is enabled use
name AS [Database Name],
FROM sys.sysdatabases
WHERE ([dbid] > 4) AND ([name] NOT LIKE '$')
Then the value of the STATUS2 column reveals the state.
If 536870912 it is enabled.
PS: You may get a different value then you need to convert it into binary number and seek for 0x20000000 value representing the FTS

Anonymous said...

Add the following to the WHERE clause in Arthure Zubarev's response to restrict the results to databases that have full text enabled:

AND (536870912 & status2) = 536870912