How To Enable SQL Full Text Indexing

The following steps will tell you if your database version supports full text searching and if so how to enable it.

  1. Open SQL server management console
  2. Right click on the database and select properties
  3. On the left select files
  4. If “Full-Text Indexing” is greyed out it means that full-text indexing is not enabled.
    NOTE: If you are using SQL Server 2012 Please see the SQL Server 2012 notes at the bottom of this article.

How to enable Full Text Searching

  1. Click on the New Query button top left of the screen and make sure the database is selected from the drop down list below the button.
  2. This query will tell if Full Text Search is enabled:
    SELECT DATABASEPROPERTY('REPLACE_WITH_NAME_OF_DATABASE',
                            'IsFulltextEnabled')

    then click the Execute button

  3. If you get a 1 in the results window Full Text Search is already enabled.
  4. If the result is null, you may have the database name incorrect.
  5. If the result is zero, Full Text Search can be enabled by running these queries:
    exec sp_fulltext_database 'enable'
  6. Now run this again:
    SELECT DATABASEPROPERTY('REPLACE_WITH_NAME_OF_DATABASE',
                            'IsFulltextEnabled')

If you get a one, that shows that Full Text Search is now enabled. If the response is still 0, then your installation of SQL Server cannot enable Full Text Search. Some reasons for this include:

  • You may need to change the install of SQL Server to include Full Text Search
  • Your SQL Server edition does not support Full Text Search.

You will need to ensure that any installation of SQL Server has Full Text Search enabled, standard and enterprise editions have this by default however express editions need the advanced services version to be installed. SQL Express Web Edition does not support Full Text Search at all.

Index The Tables

Once Full Text Search has been enabled you need to the build the indexes that will be used to return results of your search query.

The following will show you how to enable this and only need to be done once:

  1. Open the MS SQL Server Management Studio and login
  2. Expand the databases folder
  3. Expand the database
  4. Expand the Storage folder
  5. Right Click on Full Text Catalogs and select New Full-Text Catalog
  6. Provide the name as database_fullcatalog then click OK
  7. Right Click on Full Text Catalogs and select Refresh and you new catalog should appear

Define The Indexed Data

image

Once you have created the catalogs they need to be told what data to index:

  1. Right click on the database_fullcatalog catalog and select properties
  2. Click on Tables/Views
  3. From the table list on the left select a table and move it to the right
  4. from the list of columns tick the columns you want in the full catalog
  5. Click Ok

The catalogs will auto rebuild each time any of the data changes for the selected columns.

Another way is from the table design. Right click and select Fulltext index. Here you can choose the columns for this table.

image

SQL Server 2012 Notes

It appears that there is a bug in SQL Server 2012 SP1 where the server will report that Full Text Search is not supported in this edition of SQL Server when it clearly is. The workaround is to create the initial catalog by using a query:

CREATE FULLTEXT CATALOG database_fulltext;

Run each query seperatly against the database then you can follow the “Define The Indexed Data” section.

This bug is supposed to be fixed within the SP2 timeframe according to Microsoft.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.