Enable and Disable Full Text Search for SQL Server Databases

Problem

Full text search is enabled for all SQL Server databases once the full text components are installed for a SQL Server instance. Keep reading to understand how to enable or disable this 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 search text using the LIKE operator, but this could create a performance hit since SQL Server may not take full advantage of indexes based on how you use LIKE.

Using Full Text Search

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

Install Full Text Search feature during installation or in existing installation
Create Full Text Catalog to store full text indexes
Create Full Text Index on tables or index views
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.

After installing the feature, all SQL Server databases enable full-text search by default. I have shown this by creating a database and checked its full text search setting in the below image. I created a database called “Full_Text_Search” by running the below command to check whether the feature 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

As seen in the image below, SQL Server activated the full text search feature by default in the “Full_Text_Search” database. Note that I already installed full text search components as part of the SQL Server installation.

Disable Full Text for a Database

First, run the following T-SQL statement to find all databases with full text search enabled:

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

You will get the output below for each database. Your output may vary depending on the existing settings for this feature on your databases. We can see all user databases have full text search enabled.

Now I will disable full text search for database “AdventureWorks2019” using the T-SQL statement below:

--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 the below screenshot. Note: Previously, Microsoft suggested not using this stored procedure in your development activities because future versions may deprecate it. However, it still exists in SQL Server and works perfectly fine. We can see in the image below that the command executed successfully.

Note: This stored procedure will not remove or disable full-text catalogs created inside the database. To do this, you must manually remove it from the database.

Check Status

Let’s validate the above change for database AdventureWorks2019. The image below shows this feature for AdventureWorks2019 has been disabled.

If we try to run stored procedure sp_help_fulltext_catalogs to fetch details about catalogs created in the database, it will return the following 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.

However, disabling full-text indexing does not remove rows from sysfulltextcatalogs. Also, it does not indicate that full-text enabled tables are no longer marked for full-text indexing. All the full-text metadata definitions are still in the system tables.

We can see all full text catalogs in the respective databases. The image below shows we successfully disabled full text search for AdventureWorks2019. But its full text catalog “AW2016FullTextCatalog” still exists and is accessible.

Below is the properties page for this full text catalog to drill down inside the catalog.

As stated above, all the full-text metadata definitions are still in the system tables and accessible 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

The output below shows the full text catalog-related details.

Enable Full Text Feature in a Database

Let’s show you how to enable full text search for a database that has this feature 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. And it shows full text search for database AdventureWorks2019 as enabled.

Now, if we run stored procedure sp_help_fulltext_catalogs to fetch catalog-related information, it 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 a specific database. Read more about this feature: Step by Step Process to Add Full Text Search on SQL Server.

Now you can implement this as needed. Conduct 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, if this functionality is disabled and needed, these queries will have a major impact.

Resources

Community

Subscribe