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

By: Manvendra Singh   |   Updated: 2021-09-24   |   Comments   |   Related: > 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.

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".

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.

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.

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.

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.

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

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.

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.

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.

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.

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:

• You can also read more article on SQL Server Management Studio
• Explore more knowledge on SQL Server Database Administration

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips