How to conclude whether the full text service is installed and/or used?



  • SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')
    

    Above query returns 0

    However, the below query returns 1 for all user databases. How to conclude whether the full text service is installed and/or used?

    SELECT name,is_fulltext_enabled
    FROM sys.databases
    


  • The FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') is the important part. If it's not installed, you cannot use it.

    It will let you create a FULLTEXT CATALOG and STOPLIST, but not the INDEX.

    CREATE FULLTEXT CATALOG [ft_catalog] WITH ACCENT_SENSITIVITY = OFF
    GO
    

    CREATE FULLTEXT STOPLIST [ft_stoplist]
    AUTHORIZATION [dbo];
    GO

    go
    CREATE TABLE dbo.Test
    (
    Id int,
    CONSTRAINT PK_Test_Id PRIMARY KEY CLUSTERED (Id)
    )

    CREATE FULLTEXT INDEX ON [dbo].Test(
    [sWord] LANGUAGE 'English')
    KEY INDEX PK_Test_Id ON (Id, FILEGROUP [PRIMARY])
    WITH (CHANGE_TRACKING = AUTO, STOPLIST = [ft_stoplist])

    Msg 7609, Level 17, State 5, Line 25
    Full-Text Search is not installed, or a full-text component cannot be loaded.

    The https://docs.microsoft.com/en-us/sql/t-sql/functions/databasepropertyex-transact-sql sheds more light on the matter (emphasis mine)

    IsFulltextEnabled - Note: The value of this property now has no effect. User databases are always enabled for full-text search. A future release of SQL Server will remove this property. Do not use this property in new development work, and modify applications that currently use this property as soon as possible.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2