How to find where in the database fulltext search is enabled

Question

Post reply

How to find where in the database fulltext search is enabled

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
May 9, 2022 at 7:43 pm

#4029664

Hello,
Is there any handy script available to find in which are all the database(s) the fulltext search is enabled?

Thanks.
May 10, 2022 at 8:29 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

DanielP SSCrazy Points: 2320 More actions · Answer 1

SELECT

CASE

WHEN

FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') = 1

THEN

'INSTALLED'

ELSE

'NOT INSTALLED'

END

Can just loop through the DBs

Steve Jones - SSC Editor SSC Guru Points: 736150 More actions · Answer 2

That gives the installation of the files for Full-Text search, not if it is in use. Enabled is a strange term. It's essentially always enabled if installed, but might not be in use.

This returns 1 in my database:

SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')

However, this returns 0 rows, as there is no FTS in use.

SELECT * FROM sys.fulltext_indexes AS fi

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 3

Thank you so much for all your responses.

But how to find in which database the FTI is setup. Instead of changing the db context every time and running the query? Any handy script to try?

Thanks.

Steve Jones - SSC Editor SSC Guru Points: 736150 More actions · Answer 4

Databases are separate things.

You could query for select count(*) from sys.fulltext_indexes and run that through a loop or sp_msforeach_db to execute in every database. That's the only way you can tell is by querying every database.