How to Enable and Disable Full Text Search for SQL Server Databases


By:   |   Updated: 2021-09-24   |   Comments   |   Related: More > Full Text Search


Problem

As you may know, full text search is enabled for all SQL Server databases once the full text components are installed for a SQL Server instance. If you have a requirement to disable full text search functionality for a database, then how can you achieve this and keep full text still working on other databases on the same SQL Server instance. Keep reading this article to understand how to enable or disable the full text search feature for individual SQL Server databases.

Solution

Microsoft introduced the Full Text Search feature to perform search operations on character-based data from tables that have a large amount of text data. We can also achieve this with similar queries using the LIKE operator, but it will be costlier and it might take more time to get the results versus using Full Text Search and its functionalities.

To use full text search, you should follow the sequence below to configure and use full text search functionality.

  1. Install Full Text Search feature during installation or in existing installation
  2. Create Full Text Catalog to store full text indexes
  3. Create Full Text Index on tables or index views
  4. Write Full Text search queries using CONTAINS or FREETEXT operators to search specific words or strings

If you have not installed this component during your SQL Server installation, then you need to add it by launching the SQL Server setup for your existing SQL Server instance. I have explained how to do this in this article Step by Step Process to Add Full Text Search on SQL Server 2019.

After you install full text search then all SQL Server databases will be full-text search enabled by default. I have shown this by creating a database and then checked its full text search setting in the below image. I have created a database called "Full_Text_Search" by running the below command and checked whether full text search is enabled for this database.

--CREATE a Database Full_TEXT_Search
--Check Full Text Search is Enabled or not for that database
CREATE DATABASE Full_Text_Search
GO

SELECT name as [DBName], is_fulltext_enabled
FROM sys.databases

We can see full text search is enabled by default for the newly created database "Full_Text_Search" in the below image. Point to be noted, I already installed full text search components as part of SQL Server installation.

query results

This article will explain enabling and disabling this feature for specific databases. I am assuming you have already installed the full text search component for your SQL Server instance along with this you have created a full text catalog in database AdventureWorks2019. Now I am going to show you how to disable the full text search feature for database AdventureWorks2019 so that this feature cannot be used for this database.

Disable Full Text Catalog Feature in a Database

First, we will run the below T-SQL statement to find all databases for which full text search is enabled.  Here is the syntax:

--Check Full Text Search is Enabled or Disabled for Databases
SELECT name as [DBName], is_fulltext_enabled
FROM sys.databases

You will get the below output for each database. Your output may vary depending on the existing settings for this feature on your databases. We can see full text search is enabled for all user databases. Now I will go ahead and disable full text search for database "AdventureWorks2019".

query results

I have executed the below T-SQL statement to disable full text search for database "AdventureWorks2019".

--Disable Full Text Search for Database "AdventureWorks2019"
USE [AdventureWorks2019]
GO

EXEC sp_fulltext_database 'disable'

I have used the legacy stored procedure "sp_fulltext_database" to disable full text search as shown in below screenshot. Although Microsoft has suggested to not use this stored procedure in your development activities because this stored procedure may be deprecated in future versions, but this still exists in SQL Server 2019 and works perfectly fine. We can see the command has been executed successfully in the below image.

exec sp_fulltext_database

One thing to note, this stored procedure will not remove or disable full-text catalogs created inside the database. If you want to remove any full text catalog from a database, then you need to manually remove it from the database.

Let's validate the above change for database AdventureWorks2019. We can see this feature shows it is disabled for AdventureWorks2019.

query results

If we try to run stored procedure sp_help_fulltext_catalogs to fetch details about catalogs created in the database then it will return the below error because we have disabled full text search for this database.

Msg 15601, Level 16, State 1, Procedure sp_help_fulltext_catalogs, Line 7 [Batch Start Line 2]
Full-Text Search is not enabled for the current database. Use sp_fulltext_database to enable Full-Text Search. The functionality to disable and enable full-text search for a database is deprecated. Please change your application.


Completion time: 2020-09-19T08:23:55.4925924-07:00
error message

Although disabling full-text indexing does not remove rows from sysfulltextcatalogs a and does not indicate that full-text enabled tables are no longer marked for full-text indexing. All the full-text metadata definitions will still be in the system tables. We can see all full text catalogs in the respective databases. You can see in the below image where we have disabled full text search for database AdventureWorks2019, but its full text catalog "AW2016FullTextCatalog" still exists and can be accessed as well.

object explorer

I have launched the properties page for this full text catalog to drill down inside the catalog as shown below.

full text catalog settings

As I stated above, all the full-text metadata definitions are still in the system tables which can be accessed as needed. I used this system object sys.fulltext_catalogs to validate this point.

--Fetch Full Text Catalog details from system catalog view
USE [AdventureWorks2019]
GO

SELECT * FROM sys.fulltext_catalogs

Below is the output of the full text catalog related details.

query results

Enable Full Text Catalog Feature in a Database

Here I will show you how to enable full text search for a database for where this feature has been disabled.

--Enable Full Text Search for Database "AdventureWorks2019" 
USE [AdventureWorks2019]
GO

EXEC sp_fulltext_database 'enable'

We use the above command to enable full text search for a database.

exec sp_fulltext_database

I checked the full text search for all databases by running the below command. We can see full text search is enabled for database AdventureWorks2019.

query results

Now, if we run stored procedure sp_help_fulltext_catalogs t to fetch catalog related information then this stored procedure will display the output without any errors. This is because we have enabled the full text search feature for this database.

query results
Next Steps

In this SQL Tutorial, I have shown how to enable or disable full text search for specific database. You can read more about full text search feature in this article Step by Step Process to Add Full Text Search on SQL Server 2019.

Now you can implement this as needed. Ensure to do proper testing in lower life cycle systems before doing anything in production. Additionally, recheck if your SQL Server transactions do not have any full text search related queries otherwise these queries could have a major impact if you disable this functionality and it is needed.

Read more articles on SQL Server:






get scripts

next tip button



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

View all my tips


Article Last Updated: 2021-09-24

Comments For This Article





download














get free sql tips
agree to terms