Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed

1. Make sure you have full-text search feature installed.

   

2. Create full-text search catalog.

    use AdventureWorks
    create fulltext catalog FullTextCatalog as default
   
    select *
    from sys.fulltext_catalogs
   

3. Create full-text search index.

    create fulltext index on Production.ProductDescription(Description)
    key index PK_ProductDescription_ProductDescriptionID
   

   Before you create the index, make sure:
   - you don't already have full-text search index on the table as only one full-text search index allowed on a table
   - a unique index exists on the table. The index must be based on single-key column, that does not allow NULL.
   - full-text catalog exists. You have to specify full-text catalog name explicitly if there is no default full-text catalog.

You can do step 2 and 3 in SQL Sever Management Studio. In object explorer, right click on a table, select Full-Text index menu item and then Define Full-Text Index... sub-menu item. Full-Text indexing wizard will guide you through the process. It will also create a full-text search catalog for you if you don't have any yet.

You can find more info at MSDN

A workaround for CONTAINS: If you don't want to create a full text Index on the column, and performance is not one of your priorities you could use the LIKE statement which doesn't need any prior configuration:

Example: find all Products that contains the letter Q:

SELECT ID, ProductName
FROM [ProductsDB].[dbo].[Products]
WHERE [ProductsDB].[dbo].[Products].ProductName LIKE '%Q%'

You must define Full-Text-Index on all tables in database where you require to use a query with CONTAINS which will take sometime.

Instead you can use the LIKE which will give you instant results without the need to adjust any settings for the tables.

Example:

SELECT * FROM ChartOfAccounts WHERE AccountName LIKE '%Tax%'

The same result obtained with CONTAINS can be obtained with LIKE.

see the result: